Andrea Andrea - 1 year ago 70
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;

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

I find all index=1. There should be some
and some
. When I use a number instead of macro variable
, eg. 17685(02Jun2008) instead of
, 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?


Answer Source

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;

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download