Grabbing date from one table, converting to a macro, and using that date throughout other programs

[SASGeek]

Hello,

I'm trying to grab a date from one table, convert it to a macro, and use it extract data from other tables. The issue is the date extracted (&asof) and using it in the 2nd sql. The table variable (proc_date) uses data in the format 2022-12-15. I've tried some other solutions but nothing seems to work.

Any and all suggestions are appreciated.

Thank you

Paula

proc sql;

      connect to odbc as odbc(dsn='mine');

         create table dates as

             select * from connection to odbc

                   (select date as asof

                              from myperfectdatabase

                              where month = 12);

          disconnect from odbc;

                 select asof into :asofdt from dates;

quit;  

%put &asofdt;    /* date retrieved shows as 15dec2022 */

proc sql;

      connect to odbc as odbc(dsn='mine');

         create table otherdata as

             select * from connection to odbc

                   (select var1, var2, var3

                              from myotherperfectdatabase

                              where proc_date = &asofdt );    /*<--- the issue is using the macro here. orig data in 2022-12-15 format */

          disconnect from odbc; 

quit;  

[PaigeMiller · Accepted solution]

%put &asofdt;    /* date retrieved shows as 15dec2022 */

SAS does not consider this a date. SAS considers it a text string with no particular meaning, even if humans assign a meaning to it. So you have to convert it to an actual SAS date value, which is the number of days since 01JAN1960.

%let asofdt1=%sysfunc(inputn(&asofdt,date9.));
%put &=asofdt1;

So then, to use it in your second SQL, you want 

where proc_date = &asofdt1);

At this point, formats are irrelevant. SAS does all arithmetic and boolean operations on unformatted values, and so unformatted &asofdt1 works here.

[SASGeek]

Thank you Paige!