Use PROC IMPORT to Read External Data Files and Excel Workbooks into SAS - Exchanging Data Between SAS and Microsoft Excel - page 39

Figure 3.2: An Excel 2013 Worksheet with a Named Range Called “small_range” Highlighted.
The SAS code below shows how to read the data from the Excel named-range called “small_range” into a SAS
dataset. Because the GETNAMES=NO option is used, the variable names F1, F2, F3, and F4 that SAS
generated are relatively vague variable names; this example will address a way to correct that issue. The
DBDSOPTS= option allows you to use other SAS dataset options to change the output SAS dataset while it is
being created. The SAS RENAME= dataset option was used here to change the variable names from F1, F2, …
to more descriptive variable names. This is done in one pass over the data and makes the output file more useful
when PROC IMPORT finishes. You do not need to make another pass over the data to rename the variables.
The PROC DATASETS code adds LABEL values to the SAS dataset. The DBMS=EXCEL form of PROC
IMPORT does not allow variable labels to be modified on input of the data; therefore, other code is needed to
change the variable labels.
PROC IMPORT
DATAFILE='c:\My_Files\Shoes.xls'
DBMS=EXCEL
OUT=shoes
REPLACE;
GETNAMES=NO;
DBDSOPTS='RENAME=(F1=Subsidiary F2=Stores F3=Sales F4=Inventory)';
RANGE=small_range;
RUN;
PROC DATASETS LIBRARY=work NOLIST;
MODIFY shoes;
LABEL Subsidiary = "Subsidiary"
Stores = "Stores"
Sales = "Sales"
Inventory = "Inventory";
QUIT;