BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JacobH
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

5 REPLIES 5
chrej5am
Quartz | Level 8

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
Tourmaline | Level 20

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
jakarman
Barite | Level 11

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

---->-- ja karman --<-----
JacobH
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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