SAS: How to Import Specific Range from Excel


You can use the PROC IMPORT statement with the RANGE option to import a specific range of cells from an Excel file into SAS.

You can use the following basic syntax to do so:

/*import data from Excel file called basketball_data.xlsx*/
proc import out=my_data
    datafile="/home/u13181/basketball_data.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
    range="Sheet1$C4:E11";
run;

Here’s what each line does:

  • out: Name to give dataset once imported into SAS
  • datafile: Location of Excel file to import
  • dmbs: Format of file being imported
  • replace: Replace the file if it already exists
  • getnames: Use first row as variable names (Set to NO if first row does not contain variable names)
  • range: The range of cells to import

Note that this particular example will import the cells in the range C4:E11 of Sheet1 from the Excel file called basketball_data.xlsx.

The following examples show how to use this syntax in practice with the following Excel file called basketball_data.xlsx:

Example 1: Import Data from Excel File into SAS Without Specifying Range

We can use the following syntax to import the Excel file into a SAS dataset named my_data without specifying a specific range of cells to import:

/*import data from Excel file called basketball_data.xlsx*/
proc import out=my_data
    datafile="/home/u13181/basketball_data.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
run;

/*view dataset*/
proc print data=my_data;

Since we didn’t use the range statement to specify a range of cells to import, SAS imported all cells until it encountered data in the sheet.

Example 2: Import Data from Excel File into SAS and Specify Range

We can use the following syntax to import the Excel file into a SAS dataset named my_data and use the range option to import only a specific range of cells:

/*import specific cells from Excel file called basketball_data.xlsx*/
proc import out=my_data
    datafile="/home/u13181/basketball_data.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
    range="Sheet1$C4:E11";
run;

/*view dataset*/
proc print data=my_data;

Since we used the range statement to specify a range of cells to import, SAS imported only the cells in this range from the sheet.

Note that you can also pass a named range to the range statement.

For example, if the cell range C4:E11 had a named ranged of my_range then you could use the argument range=”my_range” in the PROC IMPORT statement instead.

Note: You can find the complete documentation for the PROC IMPORT statement in SAS here.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

How to Import CSV Files into SAS
How to Export Data from SAS to CSV File
How to Export Data from SAS to Excel

Leave a Reply

Your email address will not be published. Required fields are marked *