09-14-2012 11:12 AM
We should probably have more context. Do you want to do this using the point and click interface in EGuide (e.g. Query Builder) or can you write code?
Basically, you want to use a function, and informat, and a format.
will bring the data into a numeric variable as a SAS date (that can be done in code or as a data transformation in the Advanced Expression builder). The INPUT function reads a character field into a numeric data field using the specified format. RTFM for more.
Then apply the
format to get it to display the way you want.
There are large sections on reading and writing dates in the reference manuals.
09-14-2012 11:29 AM
Hi, I'm actually writting the code. I would like to export the results in Excel where instead of having an alphanumerical number like '20080223' I would actually have the numerical equivalent in Excel (39805) that is for 23 of febuary 2008 (Starting date = 1900).
CREATE TABLE work.date AS
SELECT t1.account_number, t1.opening_date
FROM client AS t1
WHERE t1.opening_date NOT = ' ';
09-14-2012 11:55 AM
Asof= convert '20081223' into a SAS date value ...
AS_Of=(int(asof/86400) + 21916) + ((asof-(int(asof/86400)*86400))/86400);**Build microsoft date serial;
where 86400=seconds per day, 21916 is the offset difference between day 0 in SAS and day 0 in ms
09-15-2012 04:37 PM
Who knew that Excel dates were so wonky? I had always used a fudge factor of 21916 to adjust from the Excel date to the SAS date. I think I did it based on this paper:
http://www2.sas.com/proceedings/sugi29/068-29.pdf says the "fudge factor" is 21916 but doesn't explain why.
But when Bill used 21916 and Mike recommended 21914, I thought, hmmm, I wonder why the 2 different dates. So then, I found this: http://www.sascommunity.org/wiki/Tips:Conversion_from_Excel_Date_to_SAS_Date
that explains you have to check whether Microsoft is using the 1900 date system or the 1904 date system. And then Microsoft had something to say about 1900 vs 1904:
And then I read some more and it comes down to this: apparently, Lotus 123 was off because they thought 1900 was a leap year and Excel wanted to be able to read Lotus123 files. So they adopted the same error. But that only impacts the first 2 months of 1900. See below...Excel treats 2/29/1900 as a valid date value. And, Excel starts at 1, for Jan 1, 1900 (not at 0).
In looking at it a bit more, I think that the fudge factor is a bit more complicated than a single number. I went to Excel and typed in numbers from 0 to 60 and then put in some other dates like Jan 1, 1960 and a date value from 1984. Then I copied that column of numbers and formatted it as date values, using Excel date formats (not any SAS). Then I took that same list of numbers and found out what the SAS numbers were and typed those in. So, I just used pure Excel to type in the spreadsheet. Imagine my surprise when the number 0 became Jan 0, 1900 -- interesting, wonky, but interesting. And then, 60 became Feb 29, 1900, which is invalid in SAS because 1900 was not a leap year. And, March 1, 1900 in Excel is the number 61.
Here's the short scoop on what I discovered after I took the dates and got the SAS numbers for each date:
Dec 31,1899 is not allowed in Excel but -21915 in SAS is 12/31/1899. But Excel dates start at Jan 1, 1960. well, sort of.
Jan 0, 1900 in Excel is number 0 and this is an invalid date in SAS, So there is no equivalent SAS number for Jan 0
Jan 1, 1900 in Excel is 1 and in SAS is -21914 ( So Microsoft does NOT really have a date 0. Jan 1, 1900 is the number 1.)
Feb 28, 1900 in Excel is 59 and in SAS is -21856
Feb 29,1900 in Excel is 60 and is invalid an invalid date in SAS, so there is no equivalent number
March 1, 1900 in Excel is 61 and in SAS is -21855
Jan 1, 1960 in Excel is 21916 and in SAS is 0
Jan 2, 1960 in Excel is 21917 and in SAS is 1
So it looks to me like AFTER March 1, 1900, the fudge factor is 21916, but that BEFORE March 1, 1900, the "fudge factor" is 21915 -- which you would only care about if your dates went back that far.
09-14-2012 12:08 PM
hi ... are you sure about that date value (39805) ... SAS "thinks" that there are 39499 days between a base day of 1/1/1900 and 2/23/2008
c = b-a;
d = intck('days',a,b);
format a b date9.;
assuming it really is 39499, then ...
input account_number :$5. opening_date :$8.;
select t1.account_number, t1.opening_date as faux_date,
input(t1.opening_date,yymmdd8.)+21914 as opening_date
from client as t1
number faux_date opening_date
00001 19000101 0
00002 19600101 21914
12345 20080223 39499