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

2016-11-27_17-06-05

Now select Choose Files

2016-11-27_17-09-08.jpg

Your files should now be located under My Folders

2016-11-27_17-10-52

Import Txt

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

2016-11-27_17-12-46.jpg

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

2016-11-27_17-17-04

Now check your results, you data is loaded.

2016-11-27_17-31-20.jpg

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.

2016-11-27_17-38-22.jpg

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

2016-11-27_17-41-26.jpg

 

 

Leave a Reply