How do I use proc sql's :into-function inside a macro?

[JacobH]

I am trying to execute the following code:

%MACRO sks(START,STOP);

%DO ite = &START %TO &STOP;

proc sql noprint;

select diagkode

into :diag&ite separated by ','

from sks_a(where=(diag_23=&ite));

quit;

%END;

%mend sks;

%sks(1,23);

%put &diag2;

The log says:

MLOGIC(SKS):  %DO loop index variable ITE is now 24; loop will not iterate again. MLOGIC(SKS):  Ending execution.

So it seems like it executes the whole thing. But when I run %put &diag2; I get the message WARNING: Apparent symbolic reference DIAG2 not resolved. So somehow it doesn't create the macrovariables. If I just run the proc sql by itself it works fine (see the code below) so the problem arises, when I put it inside a macro.

Any help is greatly appreciated

Bedste regards,

Jacob Hornnes

Code to the sql which runs fine:

proc sql noprint;

select diagkode

into :diag1 separated by ','

from sks_a(where=(diag_23=1));

quit;

%put &diag1;

[chrej5am]

I think your macrovariable does not exist after the macro run. Try to move the %put statement inside the macro and see what happens. If you want the macro variable to be accessible after the macro is executed you can either use the %global statement or just create the variables before the macro run.

By creation I mean for example %let diag2=;

Jakub

[LinusH · Accepted solution]

Since the SQL is executing inside the macro, they are probably defined as local.

Adding a %GLOBAL statement within %DO loop will solve your problem, if you need them outside the macro.

[jakarman]

for sure they are local what else, the result of that being removed after the execution of the macro.

[JacobH]

Thank you so much, both of you! I just changed the code, adding the %global statement, and know it works perfectly.

The now functioning code is:

%MACRO sks(START,STOP);

%DO ite = &START %TO &STOP;

%global diag&ite;

proc sql noprint;

select diagkode

into :diag&ite separated by ','

from sks_a(where=(diag_23=&ite));

quit;

%END;

%mend sks;

%sks(1,23);

%put &diag2;

%put &diag23;

[RW9]

Linus Hjorth provides the reason and solution, I would question why you are doing this.  Is there really a reason to create x number of macro variable lists?  Are you going to code all your programs using macro lists?  There are generally better ways, e.g. normalizing data, using by groups, arrays, code generation, hash tables, clever SQL joining etc.  Without knowing your requirements its a bit hard to say, but this should simplify creating macro lists (if that is what you really need to do):

data have;
  diagkode="AB"; diag_23=1; output;
  diagkode="CD"; diag_23=1; output;
  diagkode="RT"; diag_23=2; output;
run;

proc transpose data=have out=inter;
  by diag_23;
  var diagkode;
run;

data _null_;
  set inter;
  array col{2}; /* Note you can get this by max(diag_23) */
  call symput('DIAG'||strip(put(diag_23,best.)),catx(',',of col(*)));
run;
 
%put &diag1.;
%put &diag2.;