Database Reference
In-Depth Information
Figure 10.3: SAS LIBNAME Statements Valid Using 64-bit SAS on a 64-bit O/S with SAS
9.4.
LIBNAME out_data PCFILES PATH="C:\My_Excel_Files\Excel_Shoes.xlsb";
LIBNAME out_data PCFILES PATH="C:\My_Excel_Files\Excel_Shoes.xlsx";
LIBNAME out_data PCFILES PATH="C:\My_Excel_Files\Excel_Shoes.xls";
The *.xlsm version of the Excel files does not work in this configuration for a LIBNAME statement.
Example 10.2 Create an Excel File, Read It with SQL, and Then Compare
the Files
This example creates an Excel file with a LIBNAME statement, and a SAS DATA step then reads it into a
SAS dataset with PROC SQL and compares the files. The first file to be created will be an Excel 2007
formatted file. Notice the *.xlsb extension on the output file. This extension is required. There are no
macros allowed, and the Excel workbook is in binary format.
Figure 10.4: SAS Code and SQL to Create an Excel Workbook, Read the Worksheet, and
Compare.
LIBNAME out_data PCFILES PATH="C:\My_Excel_Files\Excel_Shoes.xlsb";
DATA out_data."Shoes"n;
SET SASHELP.Shoes;
RUN;
PROC SQL;
CREATE TABLE New_shoes AS
SELECT * FROM out_data."Shoes"n;
QUIT;
PROC COMPARE BASE=SASHELP.shoes COMP=New_Shoes;
RUN;
The output report from PROC COMPARE shows some minor differences in the files. The labels are not
output and minor differences appear in the FORMAT and INFORMAT values, but the data values are all
equal. The next two figures (10.5 and 10.6) are partial output listings and show the differences.
The following is true of the output shown in Figure 10.5:
It relates to the SAS dataset SASHELP.SHOES.
●
The variables were created in this file without formats or informats for the character variables.
●
Some of the numeric variables were assigned the DOLLAR12 format and informat to aid in the
display of the data.
●
Some of the variables were also given descriptive labels when the SAS dataset was created.
●
Figure 10.5: SAS Variable Characteristics of SASHELP.SHOES Dataset.
Variable Dataset Type Length Format Informat Label
----------- -------------- ----- ------ --------- ---------- --------------
Region SASHELP.SHOES Char 25
Product SASHELP.SHOES Char 14
Subsidiary SASHELP.SHOES Char 12
Stores SASHELP.SHOES Num 8 Number of Stores
Sales SASHELP.SHOES Num 8 DOLLAR12. DOLLAR12. Total Sales
Inventory SASHELP.SHOES Num 8 DOLLAR12. DOLLAR12. Total Inventory
Returns SASHELP.SHOES Num 8 DOLLAR12. DOLLAR12. Total Returns
Search WWH ::
Custom Search