BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;
6 REPLIES 6
art297
Opal | Level 21

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

 

knveraraju91
Barite | Level 11

Resolved.

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

Tom
Super User Tom
Super User

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;
Reeza
Super User

@knveraraju91 Please mark the question answered.

Ksharp
Super User

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. 

s_lassen
Meteorite | Level 14

@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
 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 846 views
  • 4 likes
  • 6 in conversation