turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- How can I convert an alpha data (20081223) that re...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-14-2012 10:54 AM

Alphanumerical data is : 20081223

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dominicrb

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Doc_Duke

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

PROC SQL;

CREATE TABLE work.date AS

SELECT t1.account_number, t1.opening_date

FROM client AS t1

WHERE t1.opening_date NOT = ' ';

QUIT;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dominicrb

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bill

09-15-2012 04:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dominicrb

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

**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**

**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**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dominicrb

09-14-2012 11:14 AM

data have;

a='20081223';

b=input(a,yymmdd8.);

format b ddmmyyd10.;

proc print;run;