CODEWITHSUNDEEP
sqlsas
Andrea
Andrea

Reputation: 73

How to change a date formatted number into number in SQL

I meet a very confusing problem when code SQL in SAS. My code is:

proc sql noprint;
   select VDte into :vdate 
   from test1;
quit;

proc sql;
   create table test3 as 
   select *, cdate>=&vdate. as index
   from test2;
quit;

I find all index=1. There should be some index=0 and some index=1. When I use a number instead of macro variable vdate, eg. 17685(02Jun2008) instead of &vdate., it works!

I also checked the VDte. Its type is numeric, format is ddmmyy10.. That is to say VDte is a number stored in SAS! But when give it to &vdate., there is some problem!!

Could someone help me to understand this situation?

Thanks, Andrea

Upvotes: 2

Views: 154

Answers (1)

Dominic Comtois
Dominic Comtois

Reputation: 10401

If your VDte has a SAS date format, you need to "clear" it before storing its value to the macro variable:

proc sql;
  select VDte format=8. 
    into :vdate
    from test1;
quit;

Then your comparison should work fine.

Note that you could also use the date9. format for creating your macro variable and then use cdate>="&vdate"d in your second query.

Upvotes: 3

Related Questions