SELECT INTO within DO loop

[tampham92]

Hi I am trying to run the codes below, but when I test the macro &resid_eq1 (%put &eq1.), SAS kept giving me this error.

"WARNING: Apparent symbolic reference EQ1 not resolved."

%let PROD = A B C;
%let ns = %sysfunc(countw(&prod.));

%macro plot;

%local i v1;
%do i=1 %to &ns.;
%let v1 = %scan(&prod., &i);

data _&v1._combined;
merge &v1._EQ1 (in=a) &v1._EQ3 (in=b);
if a;
by date;
run;

proc sql;
select Residuals_eq1 into :eq1
from &v1._EQ1
having monotonic()=max(monotonic());

select Residuals_eq3 into :eq3
from &v1._EQ3
having monotonic()=max(monotonic());

%end;
quit;

%mend plot;
%plot;

[Kurt_Bremser]

Please post the whole log that results in the WARNING (use the {i} button); I see nothing in your code as posted that could cause the WARNING.

---

@tampham92 wrote:

Hi I am trying to run the codes below, but when I test the macro &resid_eq1 (%put &eq1.), SAS kept giving me this error.

"WARNING: Apparent symbolic reference EQ1 not resolved."

%let PROD = A B C;
%let ns = %sysfunc(countw(&prod.));

%macro plot;

%local i v1;
%do i=1 %to &ns.;
%let v1 = %scan(&prod., &i);

data _&v1._combined;
merge &v1._EQ1 (in=a) &v1._EQ3 (in=b);
if a;
by date;
run;

proc sql;
select Residuals_eq1 into :eq1
from &v1._EQ1
having monotonic()=max(monotonic());

select Residuals_eq3 into :eq3
from &v1._EQ3
having monotonic()=max(monotonic());

%end;
quit;

%mend plot;
%plot;

---

[tampham92]

Here it is

25 GOPTIONS ACCESSIBLE;
26 %macro plot;
27
28 %local i v1;
29 %do i=1 %to &ns.;
30 %let v1 = %scan(&prod., &i);
31
32
33
34 data _&v1._combined;
35 merge &v1._EQ1 (in=a) &v1._EQ3 (in=b);
36 if a;
37 by date;
38 run;
39
40
41
42 proc sql;
43 select Residuals_eq1 into :eq1
44 from &v1._EQ1
45 having monotonic()=max(monotonic());
46
47
48
49 select Residuals_eq3 into :eq3
50 from &v1._EQ3
51 having monotonic()=max(monotonic());
52
53 %end;
54 quit;
55
56 %mend plot;
2 The SAS System 13:58 Monday, February 4, 2019

57 %plot;

NOTE: There were 186 observations read from the data set WORK.A_EQ1.
NOTE: There were 186 observations read from the data set WORK.A_EQ3.
NOTE: The data set WORK.A_COMBINED has 186 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: The query requires remerging summary statistics back with the original data.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

58
59 %put &eq1. &eq3.;
WARNING: Apparent symbolic reference EQ1 not resolved.
WARNING: Apparent symbolic reference EQ3 not resolved.
&eq1. &eq3.

[Kurt_Bremser]

Macro variables created in a macro end up in the local symbol table and are therefore not available outside the macro.

And make sure that your "having" clause gives you a result.

[ballardw · Accepted solution]

I suspect that you may be attempting to use macro variables generated inside your Plot macro after it has executed. By default the maco variables would be local to Plot and not exist outside. Here is an example:

%macro dummy();
proc sql noprint;
  select name into : names separated by " "
  from sashelp.class
  where age=11
  ;
quit;

%put Inside macro dummy names= &names.;
%mend;

%dummy;

%put Outside of macro dummy names= &names.;

Note the same WARNING you show appearing before the last %put.

If this describes your use then one fix would be to add a %global eg1 eg3; statement to the Plot macro definition before assigning the values.

Macro variable scope, which this is one example, can be fun to debug.