Convert SQL DateTime to SAS Date

[tstjean]

I am getting the max of a DateTime column from a SQL Server table and want to compare it to a Date from a SAS data set.

How do I change a SQL DateTime to a SAS date.

Here is part of the code. Be kind. I'm new to SAS.

%global Recent_Load;

proc sql noprint;

select max(Trans_Date) into :Recent_Load

from tbl_All_Trans;

quit;

When I do a put of &Recent_Load, I get 31OCT2015:00:00:00:000

How do I convert this to a SAS Date with no time part.

Terry

[Reeza]

DATEPART() function

[DartRodrigo · Accepted solution]

Hi mate,

You can use both, datepart() function or apply a format of date9.:

%global Recent_Load;
proc sql noprint;
select max(datepart(Trans_Date)) as max_date format=date9. into :Recent_Load
from tbl_All_Trans;
quit;

SAS Date, Time, and Datetime Values

Hope this helps

[tstjean]

Thank you for all the suggestions.

[FreelanceReinh]

Please note that the DATEPART function, that has been suggested, requires a SAS datetime value as its argument, i.e. a numeric value in seconds from 1st Jan 1960 00:00:00. Other database systems may very well use different base dates, e.g. 1st Jan 1900, but I'm not familiar with "SQL Server".

I take it that your SELECT statement writes a value like 31OCT2015:00:00:00:000 (or possibly rather 31OCT2015:00:00:00.000) into macro variable Recent_Load. However, if variable Trans_Date was an appropriate numeric variable containing SAS datetime values, Recent_Load would rather receive a value like 1761868800 (i.e. the SAS datetime value corresponding to 31OCT2015:00:00:00). If, in contrast, Trans_Date is actually a character variable containing strings such as "31OCT2015:00:00:00.000", your Recent_Load value seems plausible. However, the MAX function would then have been using lexicographic order, which would be inappropriate!

So, first make sure that Trans_Date is numeric and contains SAS datetime values.