Importing Excel Data into SAS

Deepanshu Bhalla 88 Comments ,

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
SAS : PROC IMPORT Excel

PROC IMPORT Syntax:

PROC IMPORT 
DATAFILE="filename"
OUT=SAS-data-set 
DBMS=identifier 
  REPLACE;
  SHEET="Sheet-name";
  GETNAMES=YES; 
  DATAROW=N;
  RANGE="range-name";
RUN;

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.xlsx"
Important Note

If you are using SAS OnDemand for Academics, make sure that your file is uploaded to your account before using the PROC IMPORT procedure. Follow the steps shown on this link - Upload Data to SAS OnDemand for Academics

In SAS Studio (SAS OnDemand for Academics), you can find the file which is generally available as /home/username/filename.xlsx.
For example, DATAFILE = "/home/deepanshu88us0/age.xlsx"
Make sure to specify your username in the path.

Import Data in SAS Studio
Import Excel file into SAS Studio

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 and dataset name is Age. Please note that 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 and above 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 Excel file into SAS

PROC IMPORT DATAFILE= "C:\age.xlsx" 
OUT= WORK.age
DBMS=XLSX
REPLACE;
SHEET="Sheet1"; 
GETNAMES=YES;
RUN;
  1. DATAFILE= "C:\age.xlsx" tells SAS where to find the Excel file that you want to import.
    In SAS Studio, DATAFILE = "/home/username/age.xlsx"
  2. OUT= WORK.age tells SAS to create a dataset named age stored in WORK library
  3. DBMS= XLSX tells SAS the XLSX (Excel 2007 and above) 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.
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 XLS (MS Excel 97-2003) format file into SAS

PROC IMPORT DATAFILE= "C:\age.xls" 
OUT= WORK.age
DBMS=XLS
REPLACE;
SHEET="Sheet1"; 
GETNAMES=YES;
RUN;

DBMS=XLS tells SAS to read the XLS (Excel 97-2003) format file.

Importing an excel file from specified row

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


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.xls"
 DBMS=XLS
 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. NAMEROW only works with XLS but not with XLSX.

Importing only specified columns from excel file

PROC IMPORT OUT= WORK.want (keep=id x y z)
 DATAFILE= "C:\Desktop\File1.xlsx"
 DBMS=XLSX REPLACE;
 GETNAMES=YES;
RUN;   

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.xlsx" DBMS=XLSX REPLACE;
GETNAMES=YES;
RUN;

Importing only rows that have non-missing data for all the variables

PROC IMPORT OUT= WORK.want ( WHERE=(x NE . AND y NE . z NE .))
 DATAFILE= "C:\Desktop\File1.xlsx"
 DBMS=XLSX REPLACE;
 GETNAMES=YES;
RUN; 

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 based on Specified Range

PROC IMPORT OUT= WORK.want
 DATAFILE= "C:\Desktop\File1.xlsx"
 DBMS=XLSX REPLACE;
RANGE="Sheet1$B4:E100";
GETNAMES=YES;
RUN;

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

Importing Data from Excel based on 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 : Import Excel Named Range
SAS : Using Excel Named Range
PROC IMPORT OUT= WORK.want
 DATAFILE= "C:\Desktop\File1.xlsx"
 DBMS=XLSX REPLACE;
RANGE="Info";
GETNAMES=YES;
RUN;


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 Website into SAS

filename test temp;
proc http
 url="https://www2.census.gov/acs2005/GEORES.xls"
 method="GET"
 out=test;
run;

proc import file=test
out=readin replace
dbms=xls ;
 NAMEROW=3;
 STARTROW=4;
run; 

SAS provides a method for extracting data from web pages by procedure named PROC HTTP. This method makes it easy to read data from web pages. In this case, variable name starts from row number 3 in datafile and data values start from row4 onwards. To import CSV file from website, you just need to change the DBMS=XLS to DBMS=CSV.

To learn more about other data file formats in PROC IMPORT, you can refer the tutorial below.

Complete Guide : How to Use PROC IMPORT in SAS
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 88 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
    2. take dbms=xls , and first you verify which version excel is instaled in your pc.....

      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
    3. data test;
      input id id1 id2 @@;
      1
      2
      3
      4
      5
      6
      7
      8
      9
      ;
      run;

      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
    2. Hi,
      Can u provide solved example of Titanic dataset with no of passengers survived?

      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
    2. I have a data with date format dd-mmm-yy format. How can I import the data using proc import??

      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. 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
  31. 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
    Replies
    1. Please try this

      Proc Import datafile="/folders/myfolders/Training/Test1.xlsx" out= Class1.Exceldata dbms=xlsx replace;
      Run;

      Delete
  32. "Importing an excel file from web page into SAS"

    FILENAME website url "https://www2.census.gov/acs2005/GEORES.xls" debug;

    PROC IMPORT OUT= Readin DATAFILE= website
    DBMS=xls
    REPLACE; SHEET="Sheet1"; GETNAMES=YES;
    RUN;
    ERROR: This "filename URL" access method is not supported by "proc import". Please copy the file to local disk before running the
    procedure.

    ReplyDelete
    Replies
    1. Any help would be highly appreciated. Please let me know if anyone has solution to the problem.
      Thanks!!

      Delete
    2. filename test temp;
      proc http
      url="https://www2.census.gov/acs2005/GEORES.xls"
      method="GET"
      out=test;
      run;

      proc import file=test
      out=readin replace
      dbms=xls ;
      NAMEROW=3;
      STARTROW=4;
      run;

      Delete
  33. proc import
    Datafile= "C:\SAS\Dataset.xls";
    Out = SAS-Data-Set
    DBMS = identifier
    Replace;
    Sheet= 'Customer Aquisition';
    Getnames= Yes;
    Run;

    The above code shows the below error:


    1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
    72
    73 proc import
    74 Datafile= "C:\SAS\Dataset.xls";
    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

    75 Out = SAS-Data-Set
    76 DBMS = identifier
    77 Replace;
    78 Sheet= 'Customer Aquisition';
    79 Getnames= Yes;
    80 Run;
    81
    82 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
    95

    Please help

    ReplyDelete
    Replies
    1. Try this
      proc import
      Datafile= "C:\SAS\Dataset.xls"
      Out = work.SAS
      DBMS = xls
      Replace;
      Sheet= "Customer Aquisition";
      Getnames= Yes;
      Run;

      Delete
  34. How i can import excel file with multiple sheets without opening excel file

    ReplyDelete
    Replies
    1. As per my understanding only one sheet can be open at one time in SAS. which is done by defining Sheet = "Sheet Name"

      Deepanshu Please correct me if I am wrong

      Delete
  35. Hello Deepanshu,
    The below command is not working when I provided variable name in Second Row of Excel sheet.

    PROC IMPORT DATAFILE= "E:\SAS Code Repository\Book1.xlsx"
    DBMS=XLSX
    OUT= TEMP REPLACE;
    NAMEROW=2;
    STARTROW=3; GETNAMES=YES;
    RUN;

    when I import excel file It still Takes variable name row as an observation and create Variable names as A,B,C...Z .
    Please suggest.

    Regards
    Rajat

    ReplyDelete
    Replies
    1. NAMEROW only works with XLS. Typo corrected in the article.

      Delete
  36. Please help me this
    Error: physical file does not exist,.......
    I copied and pasted the path.

    ReplyDelete
  37. WHY I CANT RUN THIS CODE:
    PROC IMPORT DATAFILE="/home/emmunozjob0/sasuser.v94/PRUEBA.xlsx"
    OUT=WORK.IPN
    DBMS=XLSX
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    RANGE="INFO";
    RUN;

    BUT I CAN RUN THIS:
    PROC IMPORT DATAFILE="/home/emmunozjob0/sasuser.v94/PRUEBA.xlsx"
    OUT=WORK.IPN
    DBMS=XLSX
    REPLACE;
    /* SHEET="Sheet1"; */
    GETNAMES=YES;
    RANGE="INFO";
    RUN;
    I DONT KNOW IF THERE IS A INTERFERENCE WITH THE SHEET AND RANGE KEYWORDS.
    I CAN RUN THE PROGRAM WHILE ONLY ONE OF THESE STATEMENTS IS ENABLED

    ReplyDelete
  38. Hello Sir,
    What is the difference between
    input IdNumber Name $ 6--20 Team $ 22--27 StartWeight EndWeight;
    and
    input IdNumber Name $ 6-20 Team $ 22-27 StartWeight EndWeight;

    ReplyDelete
  39. where can i find the files used in the tutorials? thanks

    ReplyDelete
  40. i want diffrent types of csv file are not imported successfully please help me

    ReplyDelete



  41. Proc import datafile ="/folders/myfolders/Sampleold.xls"
    Out=practice.DB5(keep=name sal)
    dbms=xls
    replace;
    sheet = "sheet1";
    getnames=yes;
    Run;

    I am writing below code but it doesnt create DB5. it shows notes and because of this it wont work. P.N : I am using university edition.

    ReplyDelete
  42. Hi deepanshu, I have an excel file in which I need to get only the lastrow and lastcolumn values from a sheet. For example iif in the sheet I have data from A1 to R10, then I need value for R10 only. This can be changed according to data. Can you help me how can I get this donme.

    ReplyDelete
  43. In excel file variable having space, how i can import those variable in SAS.
    Please help me with these.

    ReplyDelete
  44. Hi,

    can we import sasb7dat file to SASUniversity Edition ? Could u please explain
    thanks much

    ReplyDelete
  45. I imported a xlsx file and values in some column have strings length greater than 6, and SAS imported only 6 letters from string. How to solve this?

    ReplyDelete
  46. Hi I have a doubt in proc import

    while i read csv file using proc import, how to add variable in existing csv file like excel sheets

    ReplyDelete
    Replies
    1. Existing excel sheet had name age gender so I import that excel sheet using proc import datafile = "c:// ***" out = demo dbms = csv;

      in that csv file i need to add collegename marks percenatage variable means how can i add particular variable to excel sheet any sample pgm ...

      Delete
  47. Hi,

    I'm trying to import a xls file in SAS which has a very strange format. Variable name starting in cell E4 and actual data starting from E33. I can specify range of data in import by the "Range" but how can I get SAS to read in variable name from cell E4? Instead of manually deleting other info in the xls, is there anyway to call SAS to read in variable name and data correctly?

    ReplyDelete
  48. I couldnt make use of "WHERE"

    ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
    NE, OR, ^=, |, ||, ~=.

    ERROR 76-322: Syntax error, statement will be ignored.

    ReplyDelete
  49. Hi
    How to import XLS files having missing rows in XLS

    ReplyDelete
Next → ← Prev