How do I use Do to Loop Macro with Proc sql

[JMart]

Hello SAS community,

I'm trying to built a Do To loop Macro within a Proc Sql in order to create multiple tables based on on a column. Those table would have a generic column with 2 columns that will change name related to the choosen column variable in the where clause. 

Table would have the following column :

 EMPLID,
ACCOMPLISHMENT_&x,
RESULTS_LEVEL_CD_&x

Here's the code I wrote. Right now it is running the max amount of iteration needed but the macro variable keep getting stuck at 1 :

/*list of variable to be use in the name and in the where clause*/

proc sql;

select distinct t1.Num

into :numvar

from WORK.SLE_RESULT_HIST_rank t1;

QUIT;

/*max value of the list of variable*/

proc sql;

select max(t1.Num)

into :max

from WORK.SLE_RESULT_HIST_rank t1;

QUIT;

/*activate macro variable*/

%put &numvar;

%put &max;

/*Macro statement*/

%macro sqlloop(x); 

   PROC SQL; 

     %DO Num=1 %TO &max ;

       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; 

%sqlloop(&numvar);

Can someone help me fix that? Can't seem to make it iterate the way I want.

Thank you all

[PaigeMiller]

To tell you the truth, I always object when I see people taking long data layout and turning them into wide data layout. This is almost always (although there are exceptions) a poor programming practice that makes your coding more difficult. Virtually every SAS Proc was designed to work with data in the long layout. And, as you have seen, re-arranging the data via this SQL loop is more difficult than leaving the data as it is in the long layout.

But it depends on what you want to do with this data after this SQL macro does its manipulations. Please explain how the data will be used after this SQL macro loop finishes.

[yabwon · Accepted solution]

Try like this:

/* some fake data */
data WORK.SLE_RESULT_HIST_rank;
set sashelp.class;
rename
age=ACCOMPLISHMENT
height=RESULTS_LEVEL_CD
name=EMPLID
;
Num=mod(_N_,4);
run;


/* the code */
proc sql;
select distinct t1.Num
into :numvar1-
from WORK.SLE_RESULT_HIST_rank t1;
%let max=&SQLOBS.;
QUIT;
 
%put &numvar1. &&numvar&max;
%put &max;
 
%macro sqlloop(); 
   PROC SQL; 
     %DO Num=1 %TO &max ;
        %let x = &&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; 
 
%sqlloop();

I had to prepare some "fake" data.

Bart

[Tom]

You are not doing anything different inside the %DO loop.  So if you run it 5 times it makes the exact same dataset 5 times.

You seem to want to create a separate dataset for every distinct value of some variable (which is probably a terrible idea) so you need to use the VALUE of the variable to generate the code.

It will be much easier to just use the actual DATA to generate the code.

So if it is the values of NUM from SLE_RESULT_HIST_rank that you need to use to drive the code then make a dataset with the distinct value (if necessary).

proc sort nodupkey data=SLE_RESULT_HIST_rank(keep=num) out=NUMLIST;
  by num;
run;

Then use that list to generate your code:

filename code temp;
data _null_;
  set numlist end=eof;
  file code ;
  if _n_=1 then put 'data ';
  put 'SLE_' num '(drop=num rename=(accomplishment=ACCOMPLISHMENT_' num
     'results_level_cd=RESULT_LEVEL_CD_' num '))'
  ;
  if eof then put ';'
   / 'set SLE_RESULT_HIST_rank;' 
   / 'keep emplid num accomplishment results_level_cd;'
  ;
run;
data _null_;
  set numlist end=eof;
  file code mod ;
  if _n_=1 then put 'select (num);' ;
  put 'when (' num ') output SLE_' num ';' ;
  if eof then put 'end;' / 'run;' ;
run;

Then you can just use %INCLUDE to run the code.

%include code / source2;

Let's make up some data

data sle_result_hist_rank;
  length emplid $8 accomplishment $20 results_level_cd 8 num 8;
  do num=1,5,34;
    output;
  end;
run;

and try it:

837  %include code / source2;
NOTE: %INCLUDE (level 1) file CODE is (system-specific pathname).
838 +data
839 +SLE_1 (drop=num rename=(accomplishment=ACCOMPLISHMENT_1 results_level_cd=RESULT_LEVEL_CD_1 ))
840 +SLE_5 (drop=num rename=(accomplishment=ACCOMPLISHMENT_5 results_level_cd=RESULT_LEVEL_CD_5 ))
841 +SLE_34 (drop=num rename=(accomplishment=ACCOMPLISHMENT_34 results_level_cd=RESULT_LEVEL_CD_34 ))
842 +;
843 +set SLE_RESULT_HIST_rank;
844 +keep emplid num accomplishment results_level_cd;
845 +select (num);
846 +when (1 ) output SLE_1 ;
847 +when (5 ) output SLE_5 ;
848 +when (34 ) output SLE_34 ;
849 +end;
850 +run;

NOTE: The data set WORK.SLE_1 has 1 observations and 3 variables.
NOTE: The data set WORK.SLE_5 has 1 observations and 3 variables.
NOTE: The data set WORK.SLE_34 has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[s_lassen]

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;