Hello
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 ?
Please provide your code and ideally also attach a sample Excel file which illustrates the issue when using your code.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.