topic Re: SELECT INTO within DO loop in New SAS User

SELECT INTO within DO loop

tampham92 — Mon, 04 Feb 2019 18:34:54 GMT

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;

Re: SELECT INTO within DO loop

Kurt_Bremser — Mon, 04 Feb 2019 19:00:10 GMT

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;

Re: SELECT INTO within DO loop

tampham92 — Mon, 04 Feb 2019 19:04:12 GMT

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.

Re: SELECT INTO within DO loop

ballardw — Mon, 04 Feb 2019 21:08:29 GMT

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.

Re: SELECT INTO within DO loop

Kurt_Bremser — Mon, 04 Feb 2019 21:25:06 GMT

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.