DATA Step, Macro, Functions and more

Macro help

Reply
Super Contributor
Posts: 272

Macro help

Dear,

In the program below I had to create macro variables using proc sql in a macro. 

 

When I created the macro variables &a1 and &b1 outside of %macro one,  I am able to use in data step two.

 

But when I used the SQL code in the %macro one, I am getting errors when I call the macro variables &a1 and &b1 in data two.

 

The % global statement is not working here. Please help. Thank you very much

 

 

data one;
input id flag1$ flag2$;
datalines;
1 Y N
2 Y N
3 Y Y
4 Y Y
5 Y N
6 N Y 
7 N Y
8 N N
;

%macro one(var1=,var2=);
proc sql noprint;
select count(distinct id) into :&var1
from one
where flag1='Y';

select count(distinct id) into :&var2
from one
where flag2='Y';
quit;
%global a1 b1;
%mend;

%one(var1=a1,var2=b1);

%put &a1 &b1;

data two;
set one;
if flag1='Y' then ns=&a1;
if flag2='Y' then ns=&b1;
run;
PROC Star
Posts: 7,363

Re: Macro help

The problem isn't that the %global statement isn't working, it's that you applied it too late in the code. Start a new session and try:

data one;
input id flag1$ flag2$;
datalines;
1 Y N
2 Y N
3 Y Y
4 Y Y
5 Y N
6 N Y 
7 N Y
8 N N
;

%global a1 b1;
%macro one(var1=,var2=);
proc sql noprint;
select count(distinct id) into :&var1
from one
where flag1='Y';

select count(distinct id) into :&var2
from one
where flag2='Y';
quit;
%mend;

%one(var1=a1,var2=b1);

%put &a1 &b1;

data two;
set one;
if flag1='Y' then ns=&a1;
if flag2='Y' then ns=&b1;
run;

Art, CEO, AnalystFinder.com

 

Super Contributor
Posts: 272

Re: Macro help

Resolved.

I had to use %global statement before proc sql statement. Thank you

Super User
Super User
Posts: 6,501

Re: Macro help

You shut the barn door after the horse was already gone!

 

You can't change a local macro variable into a global macro variable. You have to define the macro variable before you try to write to it. Then SAS will write to the variable that it sees is already in the symbol table. Also PROC SQL will not write anything to the macro variable when no data meets the WHERE condition. So just set the value to some default BEFORE asking SQL to overwrite it.

 

%macro one(var1=,var2=);
%global &var1 &var2;
proc sql noprint;
%let &var1=0;
select count(distinct id) into :&var1
  from one
  where flag1='Y'
;

%let &var2=0;
select count(distinct id) into :&var2
  from one
  where flag2='Y'
;
quit;

%mend;
Super User
Posts: 17,840

Re: Macro help

@knveraraju91 Please mark the question answered.

Super User
Posts: 9,681

Re: Macro help

[ Edited ]

Tom has already given your detail answer .

You can not change the local macro variable into global marco variable,

But you can assign it into global macro variable. 

PROC Star
Posts: 63

Re: Macro help


knveraraju91 wrote:

Dear,

In the program below I had to create macro variables using proc sql in a macro. 

 

When I created the macro variables &a1 and &b1 outside of %macro one,  I am able to use in data step two.

 

But when I used the SQL code in the %macro one, I am getting errors when I call the macro variables &a1 and &b1 in data two.

 

The % global statement is not working here. Please help. Thank you very much

 

 

data one;
input id flag1$ flag2$;
datalines;
1 Y N
2 Y N
3 Y Y
4 Y Y
5 Y N
6 N Y 
7 N Y
8 N N
;

%macro one(var1=,var2=);
proc sql noprint;
select count(distinct id) into :&var1
from one
where flag1='Y';

select count(distinct id) into :&var2
from one
where flag2='Y';
quit;
%global a1 b1;
%mend;

%one(var1=a1,var2=b1);

%put &a1 &b1;

data two;
set one;
if flag1='Y' then ns=&a1;
if flag2='Y' then ns=&b1;
run;

As you already have been told, the variables should be declared %GLOBAL before being assigned a value in the macro, otherwise they become %LOCAL. Your program actually gives a clear warning of this in the log:

 
 ERROR: Attempt to %GLOBAL a name (A1) which exists in a local environment.
 ERROR: Attempt to %GLOBAL a name (B1) which exists in a local environment.
I do not quite understand why you create your macro the way you do, I think the dynamic choice of return parameter names is an unnecessary complication. But if that is what you want, here is a version which works:
%macro one(var1=,var2=);
%global &var1 &var2;

proc sql noprint;
select count(distinct id) into :&var1
from one
where flag1='Y';

select count(distinct id) into :&var2
from one
where flag2='Y';
quit;
%mend;
Apart from moving the %GLOBAL declaration to the top, I also changed it so that it will work with other parameter names than A1 and B1.
When working with macros that return values in macro variables (I avoid it if I have any other options) I generally try to make the code a bit more flexible, e.g.:
%macro one(var1=,var2=);
%if not %symexist(&var1) %then
  %global &var1;
%if not %symexist(&var2) %then
  %global &var2;

proc sql noprint;
select count(distinct id) into :&var1
from one
where flag1='Y';

select count(distinct id) into :&var2
from one
where flag2='Y';
quit;
%mend;
With this construct, it is possible to have the return variables %LOCAL to a calling macro, which can be an advantage at times, e.g.:
%macro two;
  %local a b;
  %one(var1=a,var2=b);
  %put &a &b;
%mend;

%two;
A macro like the %one above is OK if you are the %one and only user of it. But beware! If others start using your macro, and they try calling the return variables VAR1 and VAR2, havoc will surely follow, and hours will be spent trying to debug the program. Which is one reason for avoiding such constructs. Especially if you start making the %ONE macro more complicated, giving it other %LOCAL variables, in which case these will also have a potential conflict with the return variables.
All in all, I think it is better to always use the same names for the return parameters:
%macro one;
%if not %symexist(a1) %then %do;
  %global a1;
%put MACRO ONE: return variable A1 not defined, created as GLOBAL.;
%end; %if not %symexist(b1) %then %do; %global b1; %put MACRO ONE: return variable B1 not defined, created as GLOBAL.;
%end; proc sql noprint; select count(distinct id) into :a1 from one where flag1='Y'; select count(distinct id) into :b1 from one where flag2='Y'; quit; %mend;
Regards,
Søren
 
Ask a Question
Discussion stats
  • 6 replies
  • 151 views
  • 4 likes
  • 6 in conversation