Using SAS sql to create macro variables

Question

I'm using SAS sql to create some macro variables for later use and printing of reports.

I am creating some summary variables (mean, min, max, etc.) by group.

The following code works for the first group, but does not create the macro variables for groups two and three.

---

data scores;
  input trt  t;
cards;  
1  10
1  11
1  12
1  13
2  4
2  5
2  6
2  7
3  9
3  8
3  7
3  6
;
run;



%macro cdi;


%do i = 1 %to 3;

proc sql noprint;
  select  n(t),  nmiss(t),  mean(t),  std(t),  min(t),  max(t) 
    into  :n&i,   :miss&i,  :mean&i,   :sd&i,  :min&i,  :max&i

  from scores
  where trt = &i;
quit;

%end;

%mend cdi;
%cdi;

*****  this call shows the right values  ;
%put &n1 &miss1 &mean1 &sd1 &min1 &max1 ;


*****  this call produces error (symbolic reference not resolved)  ;
%put &n2 &miss2 &mean2 &sd2 &min2 &max2 ;

---

I'm sure I'm missing something simple, but I haven't been able to see it yet...

Answer 1

Your problem is that the macro variables you are creating are local to the macro. If you want then to exist after the macro executes, you need to define them with a %global statement inside the macro.

In other words, add this to the top of your macro definition:

%global n1 miss1 mean1 sd1 min1 max1 
        n2 miss2 mean2 sd2 min2 max2 
        n3 miss3 mean3 sd3 min3 max3 ;

Answer 2

Why are you creating macro variables of your summary values? There are really very few uses where it's not better to create this as a table for later use... and it's faster, much simpler, and more flexible.

proc sql;
create table summary as
select  trt, n(t) as n,  nmiss(t) as nmiss,  mean(t) as mean,  std(t) as std,  min(t) as min,  max(t) as max
from scores
group by trt;
quit;

Or, since you're in SAS...

proc means data=scores;
var t;
class trt;
types trt;
output out=summary n= nmiss= mean= std= min= max= /autoname;
run;

Then use it in your reports however you wish - or merge it back onto the main dataset, if that's easier. Data should be data, and macro variables should not be data, but rather program inputs.

Answer 3

Bob is correct. The solution is as simple as cut and paste:

%macro cdi;


%do i = 1 %to 3;

proc sql noprint;
  select  n(t),  nmiss(t),  mean(t),  std(t),  min(t),  max(t) 
    into  :n&i,   :miss&i,  :mean&i,   :sd&i,  :min&i,  :max&i

  from scores
  where trt = &i;
quit;

%end;
*****  this call shows the right values  ;
%put &n1 &miss1 &mean1 &sd1 &min1 &max1 ;


*****  this call NO LONGER produces error (symbolic reference not resolved)  ;
%put &n2 &miss2 &mean2 &sd2 &min2 &max2 ;

%mend cdi;
%cdi;