topic Convert SQL DateTime to SAS Date in SAS Programming

Convert SQL DateTime to SAS Date

tstjean — Tue, 03 Nov 2015 20:39:10 GMT

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

Re: Convert SQL DateTime to SAS Date

Reeza — Tue, 03 Nov 2015 20:44:04 GMT

DATEPART() function

Re: Convert SQL DateTime to SAS Date

DartRodrigo — Wed, 04 Nov 2015 13:16:58 GMT

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

Re: Convert SQL DateTime to SAS Date

FreelanceReinh — Wed, 04 Nov 2015 15:24:15 GMT

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.

Re: Convert SQL DateTime to SAS Date

tstjean — Wed, 04 Nov 2015 15:26:59 GMT

Thank you for all the suggestions.