Importing Excel Data into SAS

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

PROC IMPORT is the SAS procedure used to read data from excel into SAS. This tutorial covers how to import excel data to SAS with PROC IMPORT. Loading excel data to SAS is one of the most common task of a SAS programmer / analyst. Most of the raw data files are saved in MS Excel so we need to take it to SAS for further analysis.
SAS : PROC IMPORT Excel

PROC IMPORT Syntax:


1. DATAFILE= option tells SAS where to find the Excel file that you want to import 
(Complete filename path).

For example : DATAFILE = "C:\Desktop\age.xls" 

2. OUT= option tells SAS to create a dataset with any name of your choice. By default, the imported dataset is saved on WORK library (temporary library)    

 Examples :

i. OUT = Age
In this statement, PROC IMPORT uses the WORK library. This implies OUT = Age is equivalent to OUT = Work.Age .

ii. OUT = Input.Age. 
In this statement, PROC IMPORT uses the Input library (Permanent library).

3. DBMS= option tells SAS the type of file to read.

 Examples :

i. DBMS =  XLS for Excel 97-2003 workbooks
ii.DBMS =  XLSX for Excel 2007 - 2013 workbooks

4. REPLACE is used to overwrite the existing SAS dataset (If any) mentioned in the OUT= option.

5. SHEET= option is used to specify which sheet SAS would import.

 Examples :

i.  SHEET =  "Sheet1" - To import data from worksheet named sheet1.
ii. SHEET =  "Goal" - To import data from worksheet named Goal.


6. GETNAMES= YES tells SAS to use the first row of data as variable names.

By default, PROC IMPORT uses GETNAMES= YES. If you type GETNAMES= NO, SAS would not read variable names from first row of the sheet.

7. DATAROW= option is used to specify starting row from where SAS would import the data.

For example : DATAROW =5 tells SAS to start reading data from row number 5.

Note : 

i. When GETNAMES=YES, DATAROW must be greater than or equal to 2.
ii. When GETNAMES=NO, DATAROW must be greater than or equal to 1

8. RANGE= option is used to specify which range SAS would import.

Examples :

i. RANGE="Sheet1$B2:D10"
 This would tell SAS to import data from range B2:D10 from sheet1

ii. RANGE="Information"
 This would tell SAS to import data from excel defined name range. In the example shown above, it is Information.


Importing an XLS (MS Excel 97-2003) format file into SAS


  1. DATAFILE= "C:\age.xls" tells SAS where to find the Excel file that you want to import.
  2. OUT= WORK.age tells SAS to create a dataset named age stored in WORK library 
  3. DBMS= XLS tells SAS the XLS (Excel 97-2003) format file to read.
  4. REPLACE is used to overwrite the age dataset if it exists already.
  5. SHEET= "Sheet1" tells SAS to import data from Sheet1.
  6. GETNAMES="YES" tells SAS to use the first row of data as variable names.

Importing an XLSX (MS Excel 2007-2013) format file into SAS



DBMS=XLSX tells SAS the XLSX (Excel 2007-2013) format file to read.

Important Note -
Earlier SAS Versions before SAS9.2 does not support XLSX formatted file (Excel 2007 or later files). If your XLSX file contains records fewer than 65000 rows and 255 columns, you can save the file in XLS format by clicking on SAVE AS >> Excel 97-2003 Workbook. Later you can import the converted XLS file into SAS.

Importing an excel file from specified row



DATAROW=5 tells SAS to start reading data from row number 5. In this case, variable (column) names would be pulled from first row but column values would be extracted from row 5.

Importing variable name from other than first row

Suppose variable names are placed at second row in excel sheet.
PROC IMPORT DATAFILE= "E:\SAS Code Repository\Book1.xlsx"
 DBMS=XLSX
 OUT= TEMP  REPLACE;
 NAMEROW=2;
 STARTROW=3; GETNAMES=YES;
RUN;
NAMEROW=2 tells SAS to extract variable names from second row and STARTROW=3 is used to pull values from third row.

Importing only specified columns from excel file



The OUT = filename followed by KEEP=  statement is used to retain the desired variables. In the example shown above, we retained four variables ID,X,Y and Z.

In the same way, you can use DROP= statement to remove the variables that you don't want to retain.

For example : You don't want to import three variables say A, B and C.
PROC IMPORT OUT= WORK.want (DROP=A B C) DATAFILE= "C:\Desktop\File1.xls" DBMS=XLS REPLACE;     GETNAMES=YES;RUN; 
Importing only rows that have non-missing data for all the variables



In the example shown above, WHERE= statement is used to delete all the rows that have only missing data on variables x,y and z.

Importing data from excel using specified range



RANGE="Sheet1$B4:E100" tells SAS to import data from range B4:E100 from sheet1.

Importing data from excel using named range

Range Name: In MS Excel, it is a name that represents a cell, range of cells. You can create your own defined name.

Creating a range name is very simple. Select a cell or range of cells and Click on the Name box above Column A and Tye any name you want and Press Enter.

In the example below, Range A1:C10 is selected and then type Info in the name box and Press Enter.

SAS : Using Excel Named Range


RANGE="Info" tells SAS to import data from excel using user defined named range Info.

Rename columns while Importing

The variable names can be renamed using RENAME= option next to OUT= option.
PROC IMPORT DATAFILE= "E:\SAS Code Repository\Book1.xlsx"
 DBMS=XLSX
 OUT= TEMP (RENAME=(Score=TotalScore))  REPLACE;
 GETNAMES=YES;
RUN;

Importing an excel file from web page into SAS




SAS provides a method for extracting data from web pages - the URL access method of the FILENAME statement.This method makes it easy to read data from web pages. This doesn't work for webpages that ask user to login to get information about the desired data.

SAS Tutorials : 100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

59 Responses to "Importing Excel Data into SAS"

  1. this error is coming
    15 PROC IMPORT DATAFILE= "D:\flowersales.xlsx";
    ERROR: Output SAS data set must be provided.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.00 seconds
    cpu time 0.00 seconds

    16 OUT=Salesdata
    17 DBMS=XLSX
    18 REPLACE;
    19 SHEET="Sheet1";
    20 GETNAMES=YES;
    21 RUN;
    how to solve it.

    ReplyDelete
    Replies
    1. Remove semicolon ";" after "D:\flowersales.xlsx".

      Delete

    2. hi sr,

      I have a real time scenario


      I have a dataset with the following info

      emp_id currency$

      1234 USD

      1289 USD

      1389 INR

      1456 INR,USD

      1567 USD,GBP,INR



      I need ouput like this

      e_id currency$

      1234 USD

      1234 USD

      1289 USD

      1389 INR

      1456 INR

      1456 USD

      1567 USD

      1567 GBP

      1567 INR

      1567 INR


      how to do it,please help me

      Delete
    3. data testimp(DROP=TEMP);
      FILENAME impfile "/folders/myfolders/testimp.txt";
      INFILE impfile MISSOVER;
      RETAIN EMPID;
      INPUT EMPID 4. CURR $ 6-8 @9 TEMP$ 1. @;
      OUTPUT;
      DO WHILE(TEMP=',');
      INPUT CURR $ 3. temp$ 1. @;
      OUTPUT;
      END;
      RUN;

      Delete
  2. I got an error saying :This filename URL access method is not supported by proc import. Please copy the procedure.

    ReplyDelete
  3. Excellent tutorial i haven't seen this much explanation to every step keep going.....................

    ReplyDelete
  4. Thanks for this. How about changing the format of a variable from Character to Number or vice versa in the import?

    ReplyDelete
  5. how to import multiple worksheets with proc import?

    ReplyDelete
    Replies
    1. Check out this link - http://www.listendata.com/2013/10/sas-importing-multiple-excel-sheets-in.html

      Delete
  6. I want to import my xlsx same like xls by using datarow and namerow because i dont now the range.How can i do that?

    ReplyDelete
    Replies
    1. PROC IMPORT DATAFILE= "C:\Desktop\Excel\File1.xlsx"
      OUT= INPUT
      DBMS=XLSX REPLACE;
      SHEET="Sheet1";
      GETNAMES=YES;
      DATAROW=5;
      RUN;

      Delete
  7. Hi My code is

    PROC IMPORT DATAFILE="D:\desktop\excel\Sample_data.xlsx"
    OUT= WORK.age
    DBMS=XLSX
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    RUN;


    and the error is showingOUT= WORK.age
    17 DBMS=csv
    18 REPLACE;
    NOTE: The previous statement has been deleted.
    NOTE: The previous statement has been deleted.
    19 SHEET="Sheet1";
    -----
    180
    ERROR 180-322: Statement is not valid or it is used out of proper order.

    20 GETNAMES=YES;
    21 RUN;


    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.05 seconds
    cpu time 0.04 seconds


    Please help

    ReplyDelete
  8. Hi So sry the correct one are

    PROC IMPORT DATAFILE= "D:\Desktop\age.xlsx"
    OUT= WORK.age
    DBMS=XLSX
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    RUN;




    and the error is

    OUT= WORK.age
    24 DBMS=XLSX
    25 REPLACE;
    ERROR: DBMS type XLSX not valid for import.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.00 seconds
    cpu time 0.00 seconds

    26 SHEET="Sheet1";
    27 GETNAMES=YES;
    28 RUN;

    ReplyDelete
    Replies
    1. write DBMS=excel instead

      Delete
  9. how to solve it...upper one

    please help

    ReplyDelete

  10. PROC IMPORT DATAFILE= "C:\Users\3537\Desktop\New folder\vital.XLSX"
    OUT= work.vix
    dbms= XLSX;
    SHEET="Sheet1";
    GETNAMES=YES;
    RUN;

    88888888888888888888888888888888888888888888888888888
    78 PROC IMPORT DATAFILE= "C:\Users\3537\Desktop\New folder\vital.XLSX"
    79 OUT= work.vix
    80 dbms= XLSX; ERROR: DBMS type XLSX not valid for import. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.00 seconds
    cpu time 0.00 seconds
    81 SHEET="Sheet1";
    82 GETNAMES=YES;
    83 RUN;
    plz help me how to do excel exlsx files to imort when i have i try it is working

    ReplyDelete
    Replies
    1. Which version of SAS you are using? If it is earlier version of SAS than 9.2, DBMS=XLSX would not work. If you have records lower than 65K, you can save file in XLS format and try to import XLS file. Or you can save it in CSV format.

      Delete
    2. Hi my data set is like
      1
      2
      3
      4
      5
      6
      9
      And i want output like
      1 2 3
      4 5 6
      7 8 9
      Could you please help out on this doubt

      Delete
  11. I am reading data from an excel file 2013 through SAS EG5.1 version and i have generated an excel file template for the first 30 rows i have entered the dummy values like 1 and TEST for Numeric and text attributes respectively. But When the file is getting imported to SAS dataset it is not retaining the formats. Can anyone please help me out in this.

    ReplyDelete
  12. I am reading data from an excel file 2013 through SAS EG5.1 version and i have generated an excel file template for the first 30 rows i have entered the dummy values like 1 and TEST for Numeric and text attributes respectively. But When the file is getting imported to SAS dataset it is not retaining the formats. Can anyone please help me out in this.

    ReplyDelete
    Replies
    1. Numeric variables will have length 8 bytes. Character variables will have length 4 as TEST contains 4 letters. Is that something you do not want?

      Delete
  13. PROC IMPORT DATAFILE= "C:\Users\Puspanjali Sarma\Desktop\try.xlsx"
    OUT= Myfiles.NmeData
    DBMS=XLSX
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    RUN;

    I am getting 'file doesnot exist error' even though file is saved on desktop
    ERROR: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/C:\Users\Puspanjali Sarma\Desktop\/try.xlsx.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.00 seconds
    cpu time 0.00 seconds

    63
    64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
    76

    How to solve it?

    ReplyDelete
    Replies
    1. TRY BY REPLACING XLSX EXTENSION BY XLS IN BOTH DATAFILE AND DBMS.

      Delete
  14. What is the difference between INFILE and DATAFILE?

    ReplyDelete
    Replies
    1. INFILE with INPUT in a data step is also used to import the file and it is more flexible than the PROC IMPORT with DATAFILE option. The downside of using INFILE is everything has to be manually specified whereas Proc Import will do a best guess for column type, width and informats. It guesses wrong when you have so many missing values or space in missing values which makes SAS think numeric values as character values.

      Delete
    2. Thank you Deepanshu. Excellent knowledge sharing by you.

      Delete
    3. I am trying to import a excel file and one of the cell have comments which in the format like - one example is this .
      "Site is not good,
      need improvement"
      this is one comment but after good he has written it is different line but in same cell.
      Now when i am importing the excel sheet it is pointing wrongly in excel as it is entered in different line .
      so how may i read this thing in a single line so that it is read correctly.
      Can you please help me out ?



      Delete
  15. how to create new variable after importing excel file using proc import

    ReplyDelete
  16. PROC IMPORT DATAFILE= "E:\SASUniversityEdition\myfolders\income.xls"
    OUT= WORK.age
    DBMS=XLS
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    RUN;

    I am getting below Error message in Log .Can any one help me to resolve the issues
    Error:

    Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/E:\SASUniversityEdition\myfolders\income.xls.

    ReplyDelete
    Replies
    1. Since the SAS University Edition is a UNIX virtual machine, you cannot use the E: drive location for the code. Use the path under properties for My Folder in SAS Studio '/folders/myfolders/user-specific-path/income.xls

      Delete
    2. Create a shared folder. To create a shared folder open your workstation->shared folders-. create a shared folder. upload data using this shared folder and you're done!

      Delete
    3. use xlsx file insted xls file format, xlsx consist very less size compare to xls and change DBMS= xls to xlsx. convert the xls to xlsx in the excel sheet by using the SAVE AS option. As @deepanshu bhallu said change check the drive once.

      Delete
  17. PROC IMPORT OUT= WORK.want ( WHERE=(D NE.));
    DATAFILE= "/folders/myfolders/ren.xlsx"
    DBMS=XLSX
    REPLACE;
    GETNAMES=YES;
    RUN;
    Syntax error while parsing WHERE clause.
    this option will not be recognized and ignored.

    ReplyDelete
    Replies
    1. You need to put a space after NE keyword and remove semicolon before DATAFILE option. Check out this one :
      PROC IMPORT OUT= WORK.want ( WHERE=(D NE .))
      DATAFILE= "/folders/myfolders/ren.xlsx"
      DBMS=XLSX
      REPLACE;
      GETNAMES=YES;
      RUN;

      Delete
    2. What are the variables we are talking about(x,y,z) in this case?

      Delete
    3. X,Y and Z are numeric variables having missing values. We want to keep only rows that have non-missing values in all these 3 variables.

      Delete
  18. Excellent Work. Very helpful tutorial. Thank you for all this.

    ReplyDelete
  19. Great stuff :) Thank you!

    Is there a way for me to define the type & length I want each variable to be? Or does proc import not allow this?

    Amalia

    ReplyDelete
    Replies
    1. PROC IMPORT does not allow this. Instead you should use INFILE with INPUT to accomplish this.

      Delete
  20. hey i use sas v9. i entered comman datarow=4;
    it showed error 180-300
    please help

    ReplyDelete
  21. If a file i am importing from excel has spaces in the file name, can I use A FUNCTION to determine what my dataset name should be?

    Example:
    %LET INFILE1 = 2015 Example File - Data;

    %MACRO FILES(INFILE,SHEET);
    PROC IMPORT OUT=SCAN(_&INFILE1,1," ")
    %MEND;
    %FILES(&INFILE1,sheet1)

    ReplyDelete
  22. Bit disheartening when even the really easy doesn't work. Was trying to import a file and error message tells me that the file location doesn't exist. (Aren't computers lovely when they make that sort of statement!?") Any clues anyone??? Am using SAS academic on demand Studio on Windows 10 (so I don't actually need to code this in, as I can press an upload button, but am trying to learn). I have quadruple checked that I have the right path in my IMPORT statement?
    I put my file to import in the v simple directory C:\temp.

    ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/C:\temp\CS2.xls.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):

    ReplyDelete
  23. Thank you for sharing valuable SAS tutorial

    ReplyDelete
  24. Thanks a lot for this tutorial. I am a complete novice in this field but thanks to you I now have cleared some major doubts...
    Thanks a ton...

    ReplyDelete
  25. Excellent explanation of the syntax

    ReplyDelete
  26. Hi,

    while I'm importing the data from excel sheet(xlsx) to dataset i have a truncation issue.how can I import with out truncation. and what's the maximum length I can assign. Is there any procedure to assign with out truncation.

    Thanks

    ReplyDelete
    Replies
    1. Truncation normally occurs in character variables. Hence I would suggest you to take a suitable length like 100 bytes so that there is no scope of truncation. 100 bytes is a good limit and most probably,all the values should be within this size limit.

      Delete
  27. I am trying to import a excel file and one of the cell have comments which in the format like - one example is this .
    "Site is not good,
    need improvement"
    this is one comment but after good he has written it is different line but in same cell.
    Now when i am importing the excel sheet it is pointing wrongly in excel as it is entered in different line .
    so how may i read this thing in a single line so that it is read correctly.
    Can you please help me out ?



    ReplyDelete
  28. can we use informats in proc import? I am importing an excel sheet which has dates. By default proc import is reading dates incorrectly, so I want to define informats in order to read dates correctly.

    ReplyDelete
  29. Hi!
    Thanks for these informative tutorials. Keep up the good work
    I tried range with dbms=xls but it didn't work but when i changed dbms= EXCEL it worked just fine? Mine is SAS/9.1..

    ReplyDelete
  30. This comment has been removed by the author.

    ReplyDelete
  31. i want to run a program for import xls file...but i am getting error saying Physical file does not exist???how do i import xls file and run the program???pls anyone help me out with this

    ReplyDelete
  32. Hi, sir iam trying to import excel sheet in "SAS",but some errors are occuring plllllz tell me sir,

    PROC IMPORT DATAFILE=""
    OUT=WORK.products
    DBMS=XLSX
    REPLACE;
    RUN;

    /** Print the results. **/

    PROC PRINT DATA=WORK.products; RUN;

    (1). ERROR: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/.

    (2). ERROR: File WORK.PRODUCTS.DATA does not exist.
    pllllllllllllz tell me the solution....

    ReplyDelete
  33. This comment has been removed by the author.

    ReplyDelete
  34. This comment has been removed by the author.

    ReplyDelete

Next → ← Prev