Importing Excel data into SAS (PROC IMPORT)

by

Do you want to import data into SAS? 

The easiest way to import external data in SAS is to use the PROC IMPORT procedure. PROC IMPORT procedure is used to read data from external files such as Excel, or CSV and writes it to a SAS data set. In this tutorial we’ll go through creative ways to read Excel data in SAS. 

The MS excel is the most common external file people use to store, transfer, or export data. As a SAS programmer this is the most frequent task you get to read data from different-different sources which is in excel format. 

There are multiple ways and options to consider while reading excel data in SAS but it’s up to you to decide which one best suits your requirement.

PROC IMPORT Syntax

proc import datafile="filename-with-location"
     dbms=data-source-identifier
     out=output-sas-dataset
     replace;
sheet="sheet-name";
getnames=yes/no;
datarow=N;
range="range-name";
delimiter=column-data-separator;
run;

Explanation:

DATAFILE: This option tells SAS the location of the input file to read with file name. For example: datafile= “C:\ExternalFiles\Country.xlsx”;

OUT: This is the place where you mention the name of the data set which is being created after reading the external file data.

You can either create a permanent data set by specifying library reference (like libref.output-dataset) or you can just create a temporary dataset without adding any libref.

Examples:

  • OUT=stage.Country: – It will create a permanent data set in the stage library.
  • OUT=work.Country – It will create a temporary data set in the work library.

DBMS: In SAS you can read different types of data files using PROC IMPORT. You have to specify exactly what type of file you’re providing as an input file.

  • For EXCEL file: DBMS=xlsx;
  • For CSV file: DBMS=csv;

REPLACE: You can use this option to overwrite and recreate the output data set every time you run PROC EXPORT.

SHEET: This option is used to tell SAS which sheet SAS should import. This is very important when you’ve muøtiple sheets inside the same excel.

  • SHEET= “sheet1”; → It’ll import excel data from the worksheet named as “sheet1”
  • SHEET= “Population”; → It’ll import excel data from the worksheet named as “Population”

GETNAMES: With this option (YES/NO) you can specify if your input excel data have headers in the first row.

Sometimes you get the data with no headers. In that case you should specify getnames=NO and separately mention variable names in correct order with proper format and size.

DATAROW: In case if you don’t want to read input data from the beginning of the file, you can tell SAS what would be the starting point. 

datarow=5; → SAS will start reading data from the 5th row instead of the first row. 

RANGE: If you have a requirement to read input excel from a specific range then you can mention it with the RANGE option. You can tell SAS what would be the starting point and end point. 

For example: range=“Population$A1:D10” – It will import data from the sheet “Population” starting from position A2 until D10. 

DELIMITER: SAS can read different types of files. Assume you get text file with data separated by commas or tabs or semicolon or any other characters like “|” or “&” etc. 

It’s very easy to just specify the exact delimiter and you’re good to go to read that data file in SAS. 

In this tutorial we’re going to use input excel Country.xlsx. Before we begins have a look at our input data file.

Importing excel data into SAS (Proc Import)
Country.xlsx

Importing an excel data into SAS

 proc import datafile="C:\Users\Learn SAS Code\External Files\Country.xlsx" 

     dbms=xlsx out=WORK.COUNTRY 

     replace; 

     getnames=yes; 

 run; 

It’ll import excel named “Country.xlsx” from the location “C:\Users\Learn SAS Code\External Files\Country.xlsx” and a new temporary data set will be created with the name “COUNTRY”

Importing a specific worksheet from the EXCEL file and reading from a specific ROW number.

 proc import datafile="C:\Users\Learn SAS Code\External Files\Country.xlsx" 

     dbms=xls out=WORK.COUNTRY 

     replace; 

     getnames=yes; 

     sheet = “Population”;

     datarow=10

 run; 

In this code you mention exactly which worksheet to import from the excel file by using the “sheet=” option. Also you mention datarow=10 which tells SAS to read data from the 10th row instead of the first row.

Importing an EXCEL file by specifying RANGE

proc import datafile="C:\Users\Learn SAS Code\External Files\Country.xlsx" 

     dbms=xlsx out=WORK.COUNTRY 

     replace; 

     getnames=yes; 

     range="Population$A1:D10";

 run; 

By specifying the range option you’re telling SAS to read worksheet “Population” from the excel Country.xlsx starting from position A1 until D10.

Proc import in SAS excel data range

This is how you get the final output data set WORK.COUNTRY

PROC IMPORT Country data set

Importing an EXCEL file with the data filter

proc import datafile="C:\Users\Learn SAS Code\External Files\Country.xlsx" 

     dbms=xls out=WORK.COUNTRY(where=(CountryCode="USA”))

     replace; 

     getnames=yes; 

 run; 

You can literally filter out data using the WHERE clause while reading data from the file so you can avoid loading unnecessary data in SAS.

In this example you’re only fetching data for the USA. You can adjust this by changing CountryCode to some other codes such as “CAN” , or “GBR”, etc.

Importing an EXCEL file with renaming Variable Names

proc import datafile="C:\Users\Learn SAS Code\External Files\Country.xlsx" 

     dbms=xls out=WORK.COUNTRY(rename=(CountryCode=Code))

     replace; 

     getnames=yes; 

 run; 

It is also possible to change variable names while importing excel data into SAS. This is very convenient especially when your input file has complicated or lengthy column names.

In this example just to demonstrate you we’ve changed the variable name from CountryCode to Code.

Importing an EXCEL file from website into SAS

This is very interesting to learn. There are tons of freely available data on the internet. You can write SAS code to import those data from the website and build some nice dashboard on top of that. 

The below example shows how to import excel files (available on the website) into SAS. Source data used for this demo: UN Migration Stock by Origin and Destination

filename test temp;
proc http
  url="https://www.un.org/en/development/desa/population/migration/data/estimates2/data/UN_MigrantStockByOriginAndDestination_2019.xlsx" 
 method="GET"
 out=test;
run;
proc import file=test
     dbms=xlsx out=migration 
     replace;
getnames=yes;
range="Table 1$A15:IG1000";
run;

FILENAME <FILEREF> in SAS

You can avoid mentioning file name with exact path in the PROC IMPORT statement by assigning reference (FILEREF) to the file. This is very efficient if you’ve a lengthy code where you’re reading multiple external files.

At the beginning of your SAS program you can specify all the file references. It will be easy for code reading and understanding of logic. 

Also if there are any changes in the future with regards to file name or file path, it can be easily handled looking at the FILENAME <FILEREF> statements written at the beginning.

Syntax:

FILENAME <FILEREF> “filename-with-location";

proc import datafile=FILEREF
        dbms=xlsx
        out=output-dataset
        replace;
     getnames=yes;
run;

For example:

filename fileref 'C:\Users\Learn SAS Code\External Files\Country.xlsx';

proc import datafile=fileref 
        dbms=xlsx
        out=country
        replace;
     getnames=yes;
run;

proc print data=country;
run;

SAS Viya: PROC IMPORT in SAS Studio 

If you’re using SAS Studio then you can use the DATAFILE option a little differently. If you also try to use FILENAME Fileref option with PROC IMPORT then you might encounter this error:

proc import datafile="/Users/LearnSASCode/My Folder/Files/Country.xlsx" 

     dbms=xls out=WORK.COUNTRY 

     replace; 

     getnames=yes; 

 run; 
Proc import in SAS Studio

ERROR: The path /Users/LearnSASCode/My Folder/Files/Country.xlsx is not in the list of accessible paths when SAS is in the lockdown state.

This is very common error and we do have a solution for this. You can use FILESRVC option.

Use FILESRVC :

Syntax:

Use this form to access SAS Viya files using a file Uniform Resource Identifier (URI).

FILENAME fileref FILESRVC 'file-uri' <filesrvc-options>;

Example:

filename fileref filesrvc folderpath='/Users/LearnSASCode/My Folder/Files'  filename='country.xlsx';

proc import datafile=fileref

dbms=xlsx

out=work.country replace;

getnames=yes;

run;

Free SAS Course

Learn SAS Code — The only SAS programming course available on the internet which you need to master Data Analytics, Business Intelligence (BI) and Cloud technology with tons of real life examples.

Unlock Free SAS Tutorials

FAQ

Is it possible to read excel file in SAS?

Yes, absolutely! SAS can read different types of files such as xlsx, csv, txt, etc. PROC IMPORT procedure is used to read data from the external files and writes it to SAS data sets. 

How to import data file in SAS?

PROC IMPORT procedure is used to read data from external files and loads them into SAS Data sets. You can write small proc import code with required options to import data files.

 

PROC IMPORT Syntax:

 

proc import datafile=“filename-with-location”
dbms=data-source-identifier
out=output-sas-dataset
replace;
sheet=”sheet-name”;
getnames=yes/no;
datarow=N;
range=”range-name”;
delimiter=column-data-separator;
run;