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;
%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.
%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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.