topic Re: Convert sas date to actual date in SAS Programming

Convert sas date to actual date

asherer — Thu, 06 Dec 2018 15:05:39 GMT

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

Re: Convert sas date to actual date

PeterClemmensen — Thu, 06 Dec 2018 15:10:44 GMT

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
;

Re: Convert sas date to actual date

asherer — Thu, 06 Dec 2018 15:16:07 GMT

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

Re: Convert sas date to actual date

asherer — Thu, 06 Dec 2018 15:24:32 GMT

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.

Re: Convert sas date to actual date

mkeintz — Thu, 06 Dec 2018 15:46:56 GMT

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.

Re: Convert sas date to actual date

ballardw — Thu, 06 Dec 2018 16:51:56 GMT

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

Re: Convert sas date to actual date

asherer — Thu, 06 Dec 2018 19:03:14 GMT

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;

Re: Convert sas date to actual date

mkeintz — Thu, 06 Dec 2018 19:13:13 GMT

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?

Re: Convert sas date to actual date

asherer — Thu, 06 Dec 2018 19:29:54 GMT

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.