Andrea Andrea - 5 months ago 9
SQL Question

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

Answer

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.