DATA Step, Macro, Functions and more

character date variables can be compared directly without converting numeric in Proc SQL?

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

character date variables can be compared directly without converting numeric in Proc SQL?

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? 

 


Accepted Solutions
Solution
‎01-03-2017 01:17 PM
Super User
Posts: 11,343

Re: character date variables can be compared directly without converting numeric in Proc SQL?

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,564

Re: character date variables can be compared directly without converting numeric in Proc SQL?

Posted in reply to fengyuwuzu

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.

Super User
Posts: 19,789

Re: character date variables can be compared directly without converting numeric in Proc SQL?

[ Edited ]
Posted in reply to fengyuwuzu
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. 

 

 

Solution
‎01-03-2017 01:17 PM
Super User
Posts: 11,343

Re: character date variables can be compared directly without converting numeric in Proc SQL?

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

Super Contributor
Posts: 318

Re: character date variables can be compared directly without converting numeric in Proc SQL?

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. 

☑ This topic is solved.

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

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