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...

BellevueBob · Accepted Answer · 2013-02-07 19:14:58Z

4

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 ;

answered Feb 7, 2013 at 19:14

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BellevueBob Over a year ago

@blueandgrey More an old fart than rock star. Saw your profile; about 15 yrs. ago I did some SAS programming for the UI Spine Center and still have my giant souvenir coffee cup, which I take to work whenever Iowa beats Michigan to annoy a colleague. So Go Hawkeyes!

blue and grey Over a year ago

That's right! Go Hawks! Except when we get beat by Michigan, and Minnesota, and... well...

Joe · Accepted Answer · 2013-02-07 19:34:19Z

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.

amerikashka · Accepted Answer · 2013-02-07 19:25:03Z

-2

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;

answered Feb 7, 2013 at 19:25

amerikashka

1831 gold badge1 silver badge6 bronze badges

2 Comments

Joe Over a year ago

This does nothing whatsoever to solve the problem, though it does illustrate the scoping problem.

amerikashka Over a year ago

Ah, sorry should have read more carefully. Thought he was just trying to print to log.

Collectives™ on Stack Overflow

Using SAS sql to create macro variables

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related