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

39 VAR1 $
40 VAR2 $
41 VAR3 $
42 VAR4
43 VAR5 $
44 VAR6 $
45 VAR7 $
46 ;
47 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection
macro variable */
48 run;
NOTE: The infile 'c:\My_Files\Shoes.txt' is:
Filename=c:\My_Files\Shoes.txt,
RECFM=V,LRECL=32767,File Size (bytes)=24901,
Last Modified=17Feb2014:15:55:41,
Create Time=17Feb2014:16:14:58
NOTE: 395 records were read from the infile 'c:\My_Files\Shoes.txt'.
The minimum record length was 37.
The maximum record length was 85.
NOTE: The data set WORK.SHOES has 395 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds
395 rows created in WORK.SHOES from c:\My_Files\Shoes.txt.
NOTE: WORK.SHOES data set was successfully created.
NOTE: The data set WORK.SHOES has 395 observations and 7 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.53 seconds
cpu time 0.14 seconds
For SAS 6.12 and above, the External File Interface writes out “Generated SAS Datastep Code” that could be
captured and used elsewhere. The DELIMITER= statement is active only when DBMS=DLM, and this tells
PROC IMPORT what character separates the data values within the input file. When DBMS= has a value of
CSV or TAB, SAS assumes a delimiter of a comma or Tab character, respectively. The fact that the file name
was “Shoes.txt” caused the “ file-format-specific-statement” DELIMITER=DLM to identify the input file as a
text file with values separated by commas not the default of spaces for *.txt files.
Example 3.3 PROC IMPORT Using the DBMS=EXCEL Option
Example 3.3 - Part 1
The code in parts 1, 2, and 3 of Example 2.3 in Chapter 2 showed how to create an Excel workbook with
different numbers of worksheets. The example shows how to create worksheet names with mixed-case letters in
the name. However, this method will not write an Excel worksheet with a blank in the sheet name. The
following code will read the Excel file and produce a SAS dataset called “Shoes” in the Work directory. Notice
that the RANGE= value for the spreadsheet name was in capital letters and ended in a Dollar sign “$”. The
spreadsheet name in the “RANGE=” statement did not need to be in uppercase letters.
PROC IMPORT
DATAFILE='c:\My_Files\Shoes.xls'
DBMS=EXCEL
OUT=shoes
REPLACE;
RANGE='SHOES$'n;
RUN;