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

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

fengyuwuzu — Tue, 03 Jan 2017 01:59:14 GMT

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?

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

Shmuel — Tue, 03 Jan 2017 02:05:48 GMT

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.

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

Reeza — Tue, 03 Jan 2017 02:41:01 GMT

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.

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

ballardw — Tue, 03 Jan 2017 16:17:22 GMT

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

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

fengyuwuzu — Tue, 03 Jan 2017 18:19:21 GMT

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.