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