SAS: Importing .txt .cvs and .xlsx files

SAS is a data based system. It is not much use if you can’t get data into it. In the following exercises, I will show you how to import common data files.

You can download some practice data files here: examplefiles

This is a zip file. Unzip the folder and copy the contents (three files) into a place of your choosing. Now open up SAS Studio:

Select MyFolders on the left pain and select the upload files icon

Now select Choose Files

Your files should now be located under My Folders

Import Txt

If you double click on mileage.txt in the left panel, a preview of the data will pop up.

Now to import this information into SAS, we use the following code.

DATA mileage;
INFILE '/folders/myfolders/mileage.txt';
INPUT year miles;
RUN;

DATA – names your data set

INFILE choose data source

INPUT – names variables(columns)

Now click the Run icon

Now check your results, you data is loaded.

A quick note, SAS chooses a single space as the default delimiter. If your file has a different delimiter like comma.

INFILE '/folders/myfolders/mileage.txt' DLM=",";

tab delimited
INFILE '/folders/myfolders/mileage.txt' DLM= '09'x

Import CSV

Now lets import our CSV file

DATA salaryHosp;
INFILE "/folders/myfolders/salaryHosp.csv" DSD Firstobs=2;
INPUT job$ years salary;
RUN;

DSD helps to deal with missing values

Firstobs = 2 lets SAS know to start collecting data on the second row – since the first row in our CSV file is headers.

Import Excel

Importing Excel is pretty straight forward. Use the following code and SAS will take headers from the Excel file to name your variables.

proc import out=sensors DATAFILE="/folders/myfolders/sensors.xlsx" 
                   DBMS=XLSx;
RUN;

the results