when comparing date variables in data steps, we have to convert the character date variables to numeric first with input statement.
But in proc sql, it seems the character date variables can be directly compared.
In the following code, without converting to numeric, the character variables can be compared just like numeric:
proc sql;
create table lb as
select a.*, b.visit, b.visitnum
from lb_ as a
left join sv as b
on a.usubjid=b.usubjid and (b.svstdtc <=a.lbdtc <= b.svendtc);
quit;
Does Proc Sql automatically convert the character date variables to numeric for the comparison?
To expand on @Reeza's comment:
10Jan2017 will be before 9Jan2016 in character comparison because 1 < 9, first characters compared.
Or Aug 10, 2016 comes before Jan 10, 2017 because A < J
IF your character values are in the form of YYYYMMDD and have zeroes as place holders then you would be okay as 20170110 comes after 20170109 in character comparisons. Any other character valued dates are likely to fail and some quite spectacularly.
Also conversion to SAS date values will help you clean up bad dates such as Feb 30 and my favorite from an inherited data project August 67, 1985
Does compared variables have same type in both datasets ?
If yes - then probably it is compared char to char.
If no - are you sure you get the right results ?
Please post sample of both datasets and their data types.
But in proc sql, it seems the character date variables can be directly compared.
You CAN compare character date variables in a data step as well as SQL, but it will be alphabetic order, not numerical/calendar order.
EDIT: The variable types must be the same in either method, SQL or data step.
To expand on @Reeza's comment:
10Jan2017 will be before 9Jan2016 in character comparison because 1 < 9, first characters compared.
Or Aug 10, 2016 comes before Jan 10, 2017 because A < J
IF your character values are in the form of YYYYMMDD and have zeroes as place holders then you would be okay as 20170110 comes after 20170109 in character comparisons. Any other character valued dates are likely to fail and some quite spectacularly.
Also conversion to SAS date values will help you clean up bad dates such as Feb 30 and my favorite from an inherited data project August 67, 1985
Yes, my date is in yyyymmdd format (like '2015-09-03'). Now I understand why it runs okay.
I did compare with converting to numeric values, and there is no difference. The key is the character date has to be in yyyymmdd format.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.