DATA Step, Macro, Functions and more

Convert SQL DateTime to SAS Date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Convert SQL DateTime to SAS Date

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


Accepted Solutions
Solution
‎11-04-2015 10:26 AM
Regular Contributor
Posts: 212

Re: Convert SQL DateTime to SAS Date

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Convert SQL DateTime to SAS Date

DATEPART() function
Solution
‎11-04-2015 10:26 AM
Regular Contributor
Posts: 212

Re: Convert SQL DateTime to SAS Date

[ Edited ]

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

Occasional Contributor
Posts: 6

Re: Convert SQL DateTime to SAS Date

Thank you for all the suggestions.

 

Trusted Advisor
Posts: 1,115

Re: Convert SQL DateTime to SAS Date

[ Edited ]

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 589 views
  • 0 likes
  • 4 in conversation