DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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 Smiley Happy

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;


Accepted Solutions
Solution
‎01-19-2015 09:21 AM
Super User
Posts: 5,257

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

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.

Data never sleeps

View solution in original post


All Replies
Contributor
Posts: 42

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

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

Solution
‎01-19-2015 09:21 AM
Super User
Posts: 5,257

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

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.

Data never sleeps
Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----
Occasional Contributor
Posts: 6

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

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;

Super User
Super User
Posts: 7,407

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

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.;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 255 views
  • 3 likes
  • 5 in conversation