topic Re: Grabbing date from one table, converting to a macro, and using that date throughout other progra in SAS Programming

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

SASGeek — Thu, 22 Dec 2022 18:56:48 GMT

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;

Re: Grabbing date from one table, converting to a macro, and using that date throughout other progra

PaigeMiller — Thu, 22 Dec 2022 19:01:20 GMT

%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.

Re: Grabbing date from one table, converting to a macro, and using that date throughout other progra

SASGeek — Thu, 12 Jan 2023 15:45:47 GMT

Thank you Paige!