Convert a marcro variables into macro

Question

My code1 below creates a where clause in Proc SQL based on user prompt input('Str' for store number) I'd like to use a Macro (see sample code2 below) to replace the Macro variable. How can I make it work please? Thanks!

Code 1:

%global STR_COUNT STR;
%let STR_WHERE_CLAUSE=;
data _null_;
if missing(symget('str'))=0 then
do;
length STR_LIST $1000; 
STR1=symget('STR');
STR2=put(input(STR1,best4.),z4.);
STR_LIST=quote(STR2);
put STR_LIST;
end;
if missing(STR_LIST)=0 then
call symputx('STR_WHERE_CLAUSE',cats(' and T1.STR_SITE_NUM in (',STR_LIST,')'));
run;
%PUT &STR_Where_Clause;

Code 2:

%macro condition3(table=);
and &table..store in ('1234')
%mend condition3;

then I can use the macro in SQL just like the macro variable.

select xxx from t1, t2 where condition1
and condition2
%condition3(table=t6)

If you add more details with an example of what you're trying to accomplish you will have a better chance of getting an answer. — Curly_Jefferson
@Curly_Jefferson <code> select xxx from t1, t2 where condition1 <code> and condition2 <code> %condition3 this variable resolves to "and T1.STR_SITE_NUM in ('1234')". 1234 is from the user input into STR_List (see code1) now I'd like to use Macro instead of Macro variable. So, when I have to change the "from T1, T2" to "from T6, T2" in SQL, then it will be like code2 above. or maybe it's impossible? — ddss12

SRSwift SRSwift · Accepted Answer · 2014-09-18T17:45:10

I'm not sure I entirely understand your question. However, if you are trying to wrap Code 1 in a macro and replace the datastep logic with macro functions this should get you there:

%macro condition3(table, STR);
    %let STR_LIST = %sysfunc(putn(STR, z4.));
    and &table..store in ("&STR_LIST.")
%mend condition3;

proc sql;
    select xxx 
    from 
        t1, 
        t2 
    where 
        condition1 and
        condition2
        %condition3(t6, 34);
quit;

Convert a marcro variables into macro

1 Answers