i have a loop inside another loop
When i try to run it i get the below warning
WARNING: Apparent symbolic reference MCR_ not resolved.
data a;
input var1 $ var2;
datalines;
A 12
B 19
C 23
D 40
E 20
F 18
;
run;
data b;
input var1 $ var2;
datalines;
B 19
D 40
;
run;
data c;
input var1 $ var2;
datalines;
A 12
E 20
;
run;
%let data = a b c;
%macro loop;
%let n=%sysfunc(countw(&data));
%do i=1 %to &n;
%let dt = %scan(&data,&i);
PROC SQL;
create table excl_final as
Select a.var1 from A A full outer join &dt B On b.var1 = a.var1
Where A.var1 is null OR B.var1 is null;
select var1 into :mcr_&i separated by ' ' from excl_final;
Quit;
%macro ks;
%let k=%sysfunc(countw(&mcr_&i));
%do j=1 %to &k;
%let val = %scan(&mcr_&i,&j);
data final_&j;
var1=&val;
run;
%end;
%mend;
%ks;
%end;
%mend;
%loop;
In the first iteration of the macro loop, you join a with itself, which means that this condition:
where A.var1 is null or B.var1 is null
can never be true, so excl_final is created with 0 rows, which means that the SELECT INTO will not create the macro variable.
In the subsequent iterations, the first join results in a dataset with more than 0 rows, and you get the macro variable.
Remove a from macro variable &data, and add quotes around &val later to avoid the "uninitialized" NOTE:
data a;
input var1 $ var2;
datalines;
A 12
B 19
C 23
D 40
E 20
F 18
;
data b;
input var1 $ var2;
datalines;
B 19
D 40
;
data c;
input var1 $ var2;
datalines;
A 12
E 20
;
%let data = b c;
%macro loop;
%let n=%sysfunc(countw(&data));
%do i=1 %to &n;
%let dt = %scan(&data,&i);
proc sql noprint;
create table excl_final as
select a.var1
from A A full outer join &dt B on b.var1 = a.var1
where A.var1 is null or B.var1 is null
;
select var1 into :mcr_&i separated by ' ' from excl_final;
quit;
%let k=%sysfunc(countw(&&mcr_&i));
%do j=1 %to &k;
%let val = %scan(&&mcr_&i,&j);
data final_&j;
var1="&val";
run;
%end;
%end;
%mend;
%loop;
But this code looks much too complicated for what it does. You compare two datasets to find the keys that have no match in both, and then create a series of datasets where each contains only one non-matching key? What do you try to do with the resulting datasets (which, on top, will be overwritten in every iteration of the outer %DO loop)?
Quite often bad things come to those who define macros inside another macro.
First step in macro programming is to have the code work without macros. Can you show the example of the code that worked before you introduced the macros?
Second: Set Options MPRINT; to show all the code that the macros generate why attempting to execute. In this case you may also need to add SYMBOLGEN as well to trace how some of those macro variables are (attempting to) resolved.
You never defined a macro variable named MCR_. Just macro variables named MCR_1 or MCR_2 etc.
But you are trying to expand MCR_ and append the value of the macro variable I to it in two places.
%let k=%sysfunc(countw(&mcr_&i));
...
%let val = %scan(&mcr_&i,&j);
If you want to reference MCR_1 when &I is 1 then you need to use:
&&MCR_&I
On the first pass he && is replaced by & and a flag is set to remind the macro processor it needs to make a second pass. hen &I is replaced. Now you have &MCR_1 and that can be evaluated.
But there really does not appear to any reason to add the &I to the end of the macro variable's name.
Nor any reason to define the KS macro at all. (NOTE if you do need to define multiple macro define them independently. Enclosing the definition of a macro inside the definition of another macro is just going to confuse the programmers. The address space for macros is flat. There can only be on KS macro at a time.)
%macro loop;
%local i dt mcr j val;
%do i=1 %to %sysfunc(countw(&data));
%let dt = %scan(&data,&i);
proc sql noprint;
create table excl_final as
select a.var1
from A A full o
uter join &dt B
on b.var1 = a.var1
where A.var1 is null
or B.var1 is null
;
%let mcr=;
select var1 into :mcr separated by ' ' from excl_final;
quit;
%do j=1 %to %sysfunc(countw(&mcr));
%let val = %scan(&mcr,&j);
data final_&j;
var1=&val;
run;
%end;
%end;
%mend;
Also this macro does not appear to be doing anything useful at all.
If you can explain the real issue you are trying to solve perhaps a much simpler solution can be found.
In the first iteration of the macro loop, you join a with itself, which means that this condition:
where A.var1 is null or B.var1 is null
can never be true, so excl_final is created with 0 rows, which means that the SELECT INTO will not create the macro variable.
In the subsequent iterations, the first join results in a dataset with more than 0 rows, and you get the macro variable.
Remove a from macro variable &data, and add quotes around &val later to avoid the "uninitialized" NOTE:
data a;
input var1 $ var2;
datalines;
A 12
B 19
C 23
D 40
E 20
F 18
;
data b;
input var1 $ var2;
datalines;
B 19
D 40
;
data c;
input var1 $ var2;
datalines;
A 12
E 20
;
%let data = b c;
%macro loop;
%let n=%sysfunc(countw(&data));
%do i=1 %to &n;
%let dt = %scan(&data,&i);
proc sql noprint;
create table excl_final as
select a.var1
from A A full outer join &dt B on b.var1 = a.var1
where A.var1 is null or B.var1 is null
;
select var1 into :mcr_&i separated by ' ' from excl_final;
quit;
%let k=%sysfunc(countw(&&mcr_&i));
%do j=1 %to &k;
%let val = %scan(&&mcr_&i,&j);
data final_&j;
var1="&val";
run;
%end;
%end;
%mend;
%loop;
But this code looks much too complicated for what it does. You compare two datasets to find the keys that have no match in both, and then create a series of datasets where each contains only one non-matching key? What do you try to do with the resulting datasets (which, on top, will be overwritten in every iteration of the outer %DO loop)?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.