BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tstjean
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DartRodrigo
Lapis Lazuli | Level 10

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

4 REPLIES 4
Reeza
Super User
DATEPART() function
DartRodrigo
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thank you for all the suggestions.

 

FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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