Database Reference
In-Depth Information
42 Set sashelp.shoes;
43 Run;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.
NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.
NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.
NOTE: There were 395 observations read from the data set SASHELP.SHOES.
NOTE: The data set XLS_V95.shoes has 395 observations and 7 variables.
NOTE: The data set XLS_V97.shoes has 395 observations and 7 variables.
NOTE: The data set XLS_V07.shoes has 395 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.04 seconds
Example 4.5 Using Named Literals with the LIBNAME Statement
Now let's put some data into an Excel file. We will use what is called a named literal. This example creates
a new Excel workbook with one worksheet. When we create the new worksheet, we use the name “Sheet1”
to create the workbook and worksheet. When I open the SAS Explorer window, I see two items in the list
of objects that SAS can view. They are “Sheet1” and “Sheet1$”. See Figure 4.9.3 for an example. “Sheet1”
is treated as a “Named Range”. “Sheet1$” refers to all of the data on the worksheet. Either name can be
selected within a DATA step SET statement or procedure call. Either sheet icon in the Explorer window
should also be able to be selected and viewed by double-clicking on the icon and opening the SAS
“Viewtable” window. If you had defined any named ranges in any worksheets of the Excel workbook,
those named ranges would also be displayed in the SAS Explorer window. You can also view those Excel
cells. Remember, when you look at these items from the Explorer window you are looking at the Excel
data--not a SAS dataset. The code below shows one way to create an Excel worksheet and one way to read
the same full Excel worksheet.
LIBNAME xls_data EXCEL 'C:\My_excel_files\my_excel_file.xls';
DATA xls_data.'sheet1'n;
SET sashelp.shoes;
RUN;
DATA test;
set xls_data.'sheet1$'n;
RUN;
To show you how complex this processing is I want to point out the following cases that you can use when
you create an Excel worksheet. Three of these commands do the same thing. Can you guess which one does
not work? The same conditions exist when using a SET statement to read the Excel worksheet.
DATA Step Code
Status
DATA xls_data.sheet1;
Valid SAS Command
●
DATA xls_data.sheet1$;
Invalid SAS Command (unquoted dollar sign)
●
DATA xls_data.'sheet1'n;
Valid SAS Command
●
DATA xls_data.'sheet1$'n;
Valid SAS Command
●
Search WWH ::
Custom Search