I think you made a small mistake in your macro code. You forgot to give the X variable a value, I suppose it should be the current NUM value that you are looking for, e.g.:
%macro sqlloop;
%local x;
PROC SQL;
%DO Num=1 %TO &max ;
%let x=%scan(&numvar,&num);
CREATE TABLE sle_&x as
SELECT t1.EMPLID,
t1.ACCOMPLISHMENT as ACCOMPLISHMENT_&x,
t1.RESULTS_LEVEL_CD as RESULTS_LEVEL_CD_&x
FROM WORK.SLE_RESULT_HIST_rank t1
where t1.Num = &x;
%END;
QUIT;
%mend sqlloop;
Another possibility is to use a parameter, as you first thought of, and then use SQL to create the relevant macro calls:
%macro sqlget(x);
PROC SQL;
CREATE TABLE sle_&x as
SELECT t1.EMPLID,
t1.ACCOMPLISHMENT as ACCOMPLISHMENT_&x,
t1.RESULTS_LEVEL_CD as RESULTS_LEVEL_CD_&x
FROM WORK.SLE_RESULT_HIST_rank t1
where t1.Num = &x;
QUIT;
%mend sqlget;
I renamed the macro from SQLLOOP to SQLGET, as there is no loop now. To execute all the SQL calls, just use
proc sql;
select distinct cats('%SQLGET(',t1.Num,')')
into :sqlcalls separated by ';'
from WORK.SLE_RESULT_HIST_rank t1;
QUIT;
&sqlcalls;
... View more
