Convert sas date to actual date

[asherer]

I am trying to convert a sas date field to a regular date field in PROC SQL.... The field name is DateID_TransactionDate... The data  looks like this before running the code:

DateID_TransactionDate

41089

41059

41212

The code I am using is:

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_WAIVEREFUND_FACT AS

SELECT t1.DateID_TransactionDate FORMAT=DATE7. AS DateID_TransactionDate,

 FROM SQL_LIB.WaiveRefund_Fact t1;

The results I am getting is

11JUN72

21MAY72

29OCT72

These dates are incorrect and here is what I want it to show:

06/29/12

05/30/12

10/30/12

[PeterClemmensen]

I don't see how you would get the desired dates from those values? Please elaborate. Putting the desired format on them creates this

data have;
input DateID_TransactionDate;
format DateID_TransactionDate mmddyy8.;
datalines;
41089
41059
41212
;

[asherer]

So when I put the numbers

41089

41059

41212

in Microsoft Excel it gives me

06/29/12

05/30/12

10/30/12

but sas gave me  

11JUN72

21MAY72

29OCT72

The table I am pulling from has the following properties:

Type Numeric

Group Numeric

Format 11.

Informat 11

[asherer]

it seems that the numbers are based off total time from 01/01/1900 (Excel Start Time).. What would be a formula to fix it.

[mkeintz · Accepted solution]

Excel uses the data value 1 for 01jan1900, 2for 02jan190o, etc.  But SAS uses 1 for 02jan1960.  So for an excel data value, you presumably have to subtract a number equal to '02jan1960'd - '01jan1900'd = 21915.

EXCEPT  !!!   Even though 1900 is divisible by 4, there is no leap day in the Gregorian calendar for 1900.  But excel erroneously assumes there is (the putative reason was to be consistent with this error introduced in the earlier Lotus 1-2-3 spreadsheet).  So the shift in values has to be increased by 1 to accurately shift from excel date value to sas date value:  i.e. '02jan1960'd-'31dec1899'd=21916:

data _null_;
  do xval=41089,41059,41212;
    sval=xval-21916;
    put  xval= sval= sval=date9.;
  end;
run;

But be aware that, because Excel doesn't have 29feb1900, this rule only works for date on or after 01mar1900.

[asherer]

So if I run the numbers the way you have it, it works... but if I try to proc SQL its not working any suggestion...

CREATE TABLE WORK.QUERY_FOR_WAIVEREFUND_FACT AS

SELECT t1.DateID_TransactionDate-21916 FORMAT=DATE7. AS DateID_TransactionDate,

 FROM SQL_LIB.WaiveRefund_Fact t1;

[mkeintz]

What am I supposed to understand from the phrase "its not working"?   Please show what you expected, and what you got.

And, BTW, you are apparently extracting data from SQL.  Does it contain the excel data values?

[asherer]

Sorry I should of gave you more info than previously. Odd thing is after you wrote me back I has exited out and retried and its working... Sorry for saying it did not work. And thanks so much for the help... I must of had a typo.

[ballardw]

---

@asherer wrote:

I am trying to convert a sas date field to a regular date field in PROC SQL.... The field name is DateID_TransactionDate... The data  looks like this before running the code:

DateID_TransactionDate

41089

41059

41212

The code I am using is:

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_WAIVEREFUND_FACT AS

SELECT t1.DateID_TransactionDate FORMAT=DATE7. AS DateID_TransactionDate,

 FROM SQL_LIB.WaiveRefund_Fact t1;

The results I am getting is

11JUN72

21MAY72

29OCT72

These dates are incorrect and here is what I want it to show:

06/29/12

05/30/12

10/30/12

---

And you have just demonstrated a version of the Y2K issue: 2 digit years. If at all practical do not use two digit years. The 41089 would have shown as 30JUN2072 if you had used DATE9. and 4 digit years. Which would have shown that some VERY significant was going on.

I recognized this as a likely Excel date issue as we have seen that many times. You can usually get better results with dates by ensuring that an entire column is formatted as the desired date appearance in Excel and then exporting the data from Excel as CSV and reading that file into SAS. Then SAS would have been reading from a field like 06/29/2012 to start with.