Macro variable not resolved

[Toni2]

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;

[ballardw]

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.

[Astounding]

Your final %scan function works on

(&mcr_&I, &j)

You need to add another &:
(&&mcr_&I, &j)

[Tom]

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.

[Toni2]

hi thanks, when i add an additional & in the
%let k=%sysfunc(countw(&&mcr_&i));
%let val = %scan(&&mcr_&i,&j);

i get this warning
WARNING: Apparent symbolic reference MCR_1 not resolved.

[Kurt_Bremser · Accepted solution]

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)?