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

Example 3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select Columns
When using the DBMS=XLS option of PROC IMPORT with the ENDCOL and STARTCOL statements, the
output SAS dataset is restricted to only the columns requested. This works like a KEEP statement, except the
columns have to be contiguous. The input file is the SASHELP.SHOES dataset as exported to an Excel file.
This example imports columns 2, 3, and 4 (Product, Subsidiary, and Number of Stores).
NOTE: There is a comment in the SAS log about a name change for the variable named “Number of Stores”
because this text value has spaces embedded in the value. The value shown in Figure 3.5a for column 3
(Number of Stores) is the label applied to the variable named “Number_of_Stores”. Also, ENDCOL= was
placed before STARTCOL= to show the statement order is not important. The output SAS dataset has data from
three rows and five columns of the input Excel worksheet.
PROC IMPORT
DATAFILE='c:\My_Excel_Files\Shoes.xls'
DBMS=XLS
OUT=shoes
REPLACE;
ENDCOL="4";
STARTCOL="2";
RUN;
The system output log for Example 3.5 shows the name change of the variable “Number of Stores.” The log
also verifies that only three columns were output to the SAS dataset from Excel.
1 PROC IMPORT
2 DATAFILE='c:\My_Excel_Files\Shoes.xls'
3 DBMS=XLS
4 OUT=shoes
5 REPLACE;
6 ENDCOL="4";
7 STARTCOL="2";
8 RUN;
NOTE: Variable Name Change. Number of Stores -> Number_of_Stores
NOTE: The import data set has 395 observations and 3 variables.
NOTE: WORK.SHOES data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds
SAS output dataset:
In Figure 3.5a, the SAS dataset label shown for the variable Number_of_Stores has two spaces; however, the
actual variable name does not have any spaces embedded.