Database Reference
In-Depth Information
Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
6 Inventory Num 8 DOLLAR21.2 DOLLAR21.2 Inventory
2 Product Char 14 $14. $14. Product
1 Region Char 25 $25. $25. Region
7 Returns Num 8 DOLLAR21.2 DOLLAR21.2 Returns
5 Sales Num 8 DOLLAR21.2 DOLLAR21.2 Sales
4 Stores Num 8 Stores
3 Subsidiary Char 12 $12. $12. Subsidiary
Example 4.7 Using Dataset Options to Process Date and Time Values
In the next SAS code segment, the same Excel worksheet is input twice. I wanted to give an example with
date and date/time processing options, but SAS has limited data in the SASHELP library with dates in the
file. Therefore, I created a small file that uses dates. This file will be available for download from the
Author website, http://support.sas.com/publishing/authors/benjamin.html . The file has a product name,
amount, and three dates with time segments attached. It is in a CSV format and can be loaded directly into
an Excel file. I do not show the full file in Figure 4.9.5b, but the examples here will give you enough detail
to understand what is going on in the processing. The first code line assigns a libref xls_data so that we can
read the data from the sales_data.xlsx file.
There are two DATA steps that follow and read the same Excel file to produce different results. The SAS
dataset Sales_data1 was created with default options applied to all of the date fields. So, all of the date
fields were read in and are displayed with a DATE9. format. In the second SAS dataset, fields are changed
upon input using the SAS dataset option SASDATEFMT as shown in the list below.
Field Name
Processing Option Applied
Product Default as a charcter string.
Amount Default as a numeric currency value.
Date_Ordered Default as a DATE9. Value.
Date_Shipped SASDATEFMT option converted the date/time value to a TIME8. value.
Date_Delivered SASDATEFMT option converted the Excel date to a DATETIME21.2.
value
The SAS and Excel systems both recognize dates, but each system has its own opinion about how to store
and represent dates. The reason for the difference is that SAS and Excel use different base dates for their
date reference point. SAS uses a base date of 01 Jan 1960, and Excel uses the Dublin Julian Day (30 DEC
1899) as the base date.
LIBNAME xls_data EXCEL 'C:\My_excel_files\sales_data.xlsx';
DATA Sales_data1;
SET xls_data.'Sales_data$'n;
Run;
DATA Sales_data2;
SET xls_data.'Sales_data$'n
(SASDATEFMT=(Date_Shipped=TIME8. Date_Delivered=DATETIME21.2));
Run;
 
Search WWH ::




Custom Search