I would like to use a sas variable in Proc SQL from statement

[Jasuo]

Hi All, 

I would like to use a SAS variable in my proc SQL's from statement, in order to create a dynamic query to automate monthly data requests. 

I would like to retrieve a table from a server, from a specific folder. Lets call the folder "MyFolder". 

There are monthly data table uploads to this folder, using a time stamp like yyyymmdd, where dd is the last day of the previous month.

I would like my code, to always get the right date, without any extra manual work, and retrieve the appropriate table. 

%let TableDate = substr(put(intnx('month',intnx('month',&TodaysDate.,-1),0,'E'),yymmddn8.),3,6);

PROC SQL;

CREATE TABLE WORK.RandomName AS SELECT DISTINCT

ID

FROM MyFolder.Table &TableDate. _I

WHERE ID = '12345678' AND CategoryID NOT IN ('45678','123789');

QUIT;

I checked, and the TableDate returns the value I would expect it to return, the issue is, that when I try to use it in the from statement, for some reason it does not seem to translate? (Returns the code inside the variable instead of the value I desire).

May I request Your kind help on this topic? 
I have been trying to resolve this issue for more time than I would like to admit, researched the forum, but could not find anything that would help me out. 

Thanks in advance! 🙂

[Kurt_Bremser · Accepted solution]

Your macro variable contains the whole code you stored in it. When used in a DATA step, the function calls work, but the code will not work when used as part of a dataset name.
Wrap the function calls in %SYSFUNC so that they are executed during the macro variable creation.

[Jasuo]

Thank You so much, managed to make it work. I am immensely grateful!

[Kurt_Bremser]

And since %SYSFUNC allows formatting of the result, your function call would look like this:

%let TableDate = %sysfunc(intnx(month,%sysfunc(today()),-1,e),yymmn4.);

%put &=tabledate;

[PaigeMiller]

We're missing some information, like what is the value of the macro variable &TodaysDate? And do you have a LIBNAME statement for MYFOLDER? Maybe you could provide more of the code before PROC SQL.

Nevertheless, treating dates as strings is simply a difficult way to proceed. Better to treat them as dates. Something like this:

%let todaysdate = %sysfunc(today());
%let tabledate = %sysfunc(intnx(month,&todaysdate,-1,e));

PROC SQL;
CREATE TABLE WORK.RandomName AS SELECT DISTINCT ID
FROM MyFolder.Table%sysfunc(putn(&tabledate,yymmddn8.))_I
WHERE ID = '12345678' AND CategoryID NOT IN ('45678','123789');
QUIT;

Please note: in the FROM statement, there are no spaces (your original code had spaces in there which won't work)

Please note: you said "yyyymmdd, where dd is the last day of the previous month." If this is something you have designed, make your life easier by using just yyyymm instead of yyyymmdd.