I'm importing a dataset from an Excel file that contains a date column. In Excel, this column is properly a data (i.e., it's a date serial) formatted as a short date (m/d/yyyy). In EG, I'm using the Import Data task to, well, import the data. On the Define Field Attributes screen, I have the following settings for the date column:
Type: Date
Source Informat: ANYDTDTE9.
Len.: 8
Output Format: Date9.
Output Informat: ANYDTDTE9.
Now here's the confusing part: on the Advanced Options page, if I select "Import the data using SAS/Access Interface to PC Files whenever possible" (yes, I have this component), the dates all import as 01Jan1960 (and when formatted as a number, they are all 0). However, if I uncheck that box, the dates import just fine. For reference, the SAS code generated by checking the box, or leaving it blank are below. Does anyone know why simply checking that box would cause it to not import correctly, despite the same import settings?
Here's the code generated by the import data task for the SAS/ACCESS version
PROC IMPORT
DATAFILE="I:\SASWork\_TD6320_CMHPRDSASMET01_\#LN00957.xlsx"
OUT=WORK.VACATION_SWAPS_TO_LOAD2
REPLACE
DBMS=EXCEL;
RANGE="Sheet1$A1:C101";
GETNAMES=YES;
RUN;
/* --------------------------------------------------------------------
This DATA step is used to both modify the attributes of fields
imported by PROC IMPORT and perform any field type specific
conversions needed.
-------------------------------------------------------------------- */
DATA WORK.VACATION_SWAPS_TO_LOAD2;
LENGTH
FLEET $ 32
RANK $ 3
WEEK 8 ;
SET WORK.VACATION_SWAPS_TO_LOAD2;
FORMAT
FLEET $CHAR32.
RANK $CHAR3.
WEEK DATE9. ;
INFORMAT
FLEET $CHAR32.
RANK $CHAR3.
WEEK ANYDTDTE9. ;
/* ----------------------------------------------------------------
For fields that were imported as datetime values but need to be
treated as date or time values, a conversion is required in
order to extract the date or time portion from the datetime
value.
---------------------------------------------------------------- */
WEEK = DATEPART(WEEK);
RUN;
...And here's the code generated by the import data task for the non-SAS/ACCESS version
/* --------------------------------------------------------------------
This DATA step reads the data values from a temporary text file
created by the Import Data wizard. The values within the temporary
text file were extracted from the Excel source file.
-------------------------------------------------------------------- */
DATA WORK.VACATION_SWAPS_TO_LOAD;
LENGTH
FLEET $ 32
RANK $ 3
WEEK 8 ;
FORMAT
FLEET $CHAR32.
RANK $CHAR3.
WEEK DATE9. ;
INFORMAT
FLEET $CHAR32.
RANK $CHAR3.
WEEK ANYDTDTE9. ;
INFILE 'I:\SASWork\_TD6320_CMHPRDSASMET01_\#LN00954'
LRECL=27
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD ;
INPUT
FLEET : $CHAR32.
RANK : $CHAR3.
WEEK : ANYDTDTE9. ;
RUN;
... View more