topic Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes in SAS Enterprise Guide

How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

dominicrb — Fri, 14 Sep 2012 14:54:08 GMT

Alphanumerical data is : 20081223

Numerical should read : 39805 the Excel equivalent of 2008-12-23

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

Doc_Duke — Fri, 14 Sep 2012 15:12:30 GMT

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.

INPUT(alphanum,yyyymmdd8.)

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

mmddyyyy-10.

format to get it to display the way you want.

There are large sections on reading and writing dates in the reference manuals.

Doc Muhlbaier

Duke

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

Linlin — Fri, 14 Sep 2012 15:14:45 GMT

data have;

a='20081223';

b=input(a,yymmdd8.);

format b ddmmyyd10.;

proc print;run;

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

dominicrb — Fri, 14 Sep 2012 15:29:06 GMT

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).

PROC SQL;

CREATE TABLE work.date AS

SELECT t1.account_number, t1.opening_date

FROM client AS t1

WHERE t1.opening_date NOT = ' ';

QUIT;

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

Bill — Fri, 14 Sep 2012 15:55:54 GMT

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

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

MikeZdeb — Fri, 14 Sep 2012 16:08:44 GMT

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

data _null_;

a='01jan1900'd;

b='23feb2008'd;

c = b-a;

d = intck('days',a,b);

put a/b/c/d;

format a b date9.;

run;

01JAN1900

23FEB2008

39499

assuming it really is 39499, then ...

data client;

input account_number :$5. opening_date :$8.;

datalines;

00001 19000101

00002 19600101

12345 20080223

;

proc sql;

select t1.account_number, t1.opening_date as faux_date,

input(t1.opening_date,yymmdd8.)+21914 as opening_date

from client as t1

where ^missing(t1.opening_date);

quit;

account_

number faux_date opening_date

00001 19000101 0

00002 19600101 21914

12345 20080223 39499

Re: How can I convert an alpha data (20081223) that represents a date, into a numerical for excel purposes

Cynthia_sas — Sat, 15 Sep 2012 20:37:32 GMT

Hi:

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:

http://support.microsoft.com/kb/180162

and
http://support.microsoft.com/kb/214058

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.

Interesting stuff.

cynthia