09-02-2017 05:59 AM
I am using data step to import data from excel.
I have 1 column with date values that are displayed in excel with "Number " format.
(For example : in excel value 42980 represnt 02SEP2017
In sas data step i define the date varaible as a numeric and give a format ddmmyy10.
The problem is that the date appears in SAS is not 02SEP2017!
I found a solution to change the format in excel to 3/14/2001 (means mm/dd/yyyy).
The problem is that my client doesn't want to change the format in excel.
Can you offer me a solution ?
09-02-2017 10:26 AM - edited 09-02-2017 10:27 AM
Normally SAS will automatically adjust the dates for you. Where I have seen it fail is when you have dates and character strings in the same column so that SAS imports the field as character instead of numeric. When that happens it pulls over the internal number that Excel uses for the data as a string of digits. I have never seen it do that and put the number into a numeric field instead, but perhaps you had a column with mixed dates and other numbers so that SAS decided the column was numeric, but did not think it was a date?
You just need to adjust the offset. SAS uses 01JAN1960 and Excel uses 01JAN1900. Then -2 in the formula is because SAS starts from zero and Excel starts from one and Excel mistakenly thinks that 1900 was a leap year. (So if you have dates that are before 01MAR1900 the result will be off by one).
data test ; mydate=42980; shouldbe='02SEP2017'd; put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ; mydate = mydate + '01JAN1900'd -2 ; put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ; format mydate shouldbe date9. ; run;
2115 data test ; 2116 mydate=42980; 2117 shouldbe='02SEP2017'd; 2118 put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ; 2119 mydate = mydate + '01JAN1900'd -2 ; 2120 put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ; 2121 format mydate shouldbe date9. ; 2122 run; BEFORE: mydate=42,980 03SEP2077 shouldbe=02SEP2017 AFTER : mydate=21,064 02SEP2017 shouldbe=02SEP2017 NOTE: The data set WORK.TEST has 1 observations and 2 variables.