Macro variable not resolved if created within a macro program, using macro variables and SELECT INTO

[Rodcjones]

I have a process in which I would like to use a macro process to create several dozen macro variables for later use. My preferred way of creating macro variables defined through a queried dataset is PROC SQL  SELECT COUNT (...) INTO.

The code to generate the macro variables appears to work. I can see a listing such as this in the log.

SYMBOLGEN: Macro variable ADM_IN_24_XW resolves to 0

However if I try to use that same macro variable to populate a variable in a new dataset I get an error and the log gives me a warning and an error.

WARNING: Apparent symbolic reference ADM_IN_24_XW not resolved.
ERROR 22-322: Syntax error, expecting one of the following:

I am wondering if the cause of the problem comes from my macro program, or did I make a typo of some sort in the data step? I think the former so am reaching out to the community for help.

data have;
input id $ point1 $ START :mmddyy8. POINT2 $ END: MMDDYY8.;
format START END yymmdd10.;
datalines;
1 Z 2/1/20 X 2/2/20
1 X 2/2/20 W 2/3/20
1 W 2/3/20 V 2/5/20
2 A 2/1/20 B 2/2/20
2 B 2/2/20 B 2/3/20
2 B 2/3/20 B 2/6/20
2 B 2/6/20 B 2/7/20
2 B 2/7/20 C 2/10/20
2 C 2/10/20 D 2/11/20
2 D 2/11/20 E 2/12/20
;

%MACRO Agegroups(p1,p2,&DT);
Title "Adm_In_24_&P1.&P2";

proc sql;
select count ( distinct id ) into: Adm_In_24_&p1.&p2
from admits
where POINT1="&P1" AND POINT2="&P2"
and start GE "&dt"D;
quit;
%put &&Adm_In_24_&p1.&p2;

%MEND;
%Agegroups(X,W,03FEB20);
%Agegroups(B,B,03FEB20);

DATA ONE;
V=&ADM_IN_24_XW;
V2=&ADM_IN_24_BB;
RUN;

[PaigeMiller]

The whole problem here is something called "macro scope". No, not the song by Stevie Wonder called "Macro Scope", but the fact that usually macro variables created in a macro are available only in that macro or in other macros called by the macro that created the variable. Perhaps this helps: https://blogs.sas.com/content/sgf/2015/02/13/sas-macro-variables-how-to-determine-scope/

In your case, if you add a %global statement into macro agegroups, this would take care of the problem. However since you say you are creating dozens of macro variables, this gets kind of tedious, and so a better approach would be to assign values to V and V2 inside a data step inside the macro agegroups. This of course requires a re-design of what you are trying to do.

Since it appears you are trying to come up with counts, this may be a case where you don't need macros at all and PROC FREQ would put the desired counts into data set variables without the need for macro variables and without the running into the macro scope problem. Or, instead of PROC FREQ, just create a data set from PROC SQL rather than having SQL put the value of the count into a macro variable.

[Tom]

If you assign a value to a macro variable that does not already exist inside of a macro then it will be make LOCAL to the macro.

So either define the macro variable before calling the macro

%let ADM_IN_24_XW=;
%Agegroups(X,W,03FEB20);

or have the macro create the macro variable in the GLOBAL scope.  Note you will get errors if you try to create a GLOBAL macro variable when the same macro variable already exists as local in a currently running macro so it helps to check if the macro variable exists already before tying to create it as global.  

%macro Agegroups(p1,p2,&DT);
%local mvar ;
%let mvar=Adm_In_24_&p1.&p2 ;
%if not %symexist(&mvar) %then %global &mvar ;

Title "&mvar";
proc sql ;
  select count ( distinct id ) into: &mvar
  from admits
  where POINT1="&P1" AND POINT2="&P2"
    and start GE "&dt"D
  ;
quit;
%put &mvar = &&&mvar ;
%mend;

[Quentin · Accepted solution]

Hi,

The problem is the macro variable you create inside the macro is being created as a local macro variable.  Which means it only exists inside the macro, i.e. while the macro is executing. If you add a %PUT _USER_ statement inside the macro, you can list all the user-created macro variables, and show the scope of each:

%MACRO Agegroups(p1,p2,DT);
Title "Adm_In_24_&P1.&P2";

proc sql;
select count ( distinct id ) into: Adm_In_24_&p1.&p2
from have
where POINT1="&P1" AND POINT2="&P2"
and start GE "&dt"D;
quit;
%put &&Adm_In_24_&p1.&p2;

%put _user_ ; *show the scope of all user-created macro variables ;

%MEND;
%Agegroups(X,W,03FEB20)
%Agegroups(B,B,03FEB20)

Log is:

300  %Agegroups(X,W,03FEB20);
0
AGEGROUPS ADM_IN_24_XW        0
AGEGROUPS DT 03FEB20
AGEGROUPS P1 X
AGEGROUPS P2 W
AGEGROUPS SQLEXITCODE 0
AGEGROUPS SQLOBS 1
AGEGROUPS SQLOOPS 17
AGEGROUPS SQLRC 0
AGEGROUPS SQLXOBS 0
AGEGROUPS SQLXOPENERRS 0

301  %Agegroups(B,B,03FEB20);
1
AGEGROUPS ADM_IN_24_BB        1
AGEGROUPS DT 03FEB20
AGEGROUPS P1 B
AGEGROUPS P2 B
AGEGROUPS SQLEXITCODE 0
AGEGROUPS SQLOBS 1
AGEGROUPS SQLOOPS 19
AGEGROUPS SQLRC 0
AGEGROUPS SQLXOBS 0
AGEGROUPS SQLXOPENERRS 0

If you want the macro variable to exist outside of the macro, you can add a %GLOBAL statement to define it as a global macro variable, e.g.:

%MACRO Agegroups(p1,p2,DT);
%global Adm_In_24_&P1.&P2 ; 
Title "Adm_In_24_&P1.&P2";

proc sql;
select count ( distinct id ) into: Adm_In_24_&p1.&p2
from have
where POINT1="&P1" AND POINT2="&P2"
and start GE "&dt"D;
quit;
%put &&Adm_In_24_&p1.&p2;

%put _user_ ; *show the scope of all user-created macro variables ;

%MEND;
%Agegroups(X,W,03FEB20)
%Agegroups(B,B,03FEB20)

And the %PUT _USER_ will show that they are now global:

330  %Agegroups(X,W,03FEB20);
0
AGEGROUPS DT 03FEB20
AGEGROUPS P1 X
AGEGROUPS P2 W
AGEGROUPS SQLEXITCODE 0
AGEGROUPS SQLOBS 1
AGEGROUPS SQLOOPS 17
AGEGROUPS SQLRC 0
AGEGROUPS SQLXOBS 0
AGEGROUPS SQLXOPENERRS 0
GLOBAL ADM_IN_24_XW        0

331  %Agegroups(B,B,03FEB20);
1
AGEGROUPS DT 03FEB20
AGEGROUPS P1 B
AGEGROUPS P2 B
AGEGROUPS SQLEXITCODE 0
AGEGROUPS SQLOBS 1
AGEGROUPS SQLOOPS 19
AGEGROUPS SQLRC 0
AGEGROUPS SQLXOBS 0
AGEGROUPS SQLXOPENERRS 0
GLOBAL ADM_IN_24_BB        1
GLOBAL ADM_IN_24_XW        0
332
333  DATA ONE;
334  V=&ADM_IN_24_XW;
335  V2=&ADM_IN_24_BB;
336  put v= v2= ;
337  RUN;

V=0 V2=1
NOTE: The data set WORK.ONE has 1 observations and 2 variables.

As a best practice, it's a good idea to always declare the scope of macro variables, to make explicit whether they are local to the macro or global.  If you don't declare the scope, SAS has rules to guess which you intend, but you might not agree with SAS's 'guess'.

Also, as @PaigeMiller mentioned, you could redesign this to avoid the need to create a global macro variable.  There are risks to creating global macro variables; since they exist for the length of the SAS session, they can can "collide."

[PaigeMiller]

---

@Quentin wrote:

Also, as @PaigeMiller mentioned, you could redesign this to avoid the need to create a global macro variable.  There are risks to creating global macro variables; since they exist for the length of the SAS session, they can can "collide."

---

Just to be accurate, I said you could avoid the need to create ANY macro variable. And another of the risks of creating global macro variables is that they often require more programming; in this case a PROC FREQ would get the necessary counts without macro variables at all, a probably without macros, and would be a whole lot simpler than all the macro code shown above.

[Rodcjones]

Thank you @Quentin, @PaigeMiller and @Tom for your super quick and thorough replies. The %global was the concept I was missing and it is going to be great knowledge for me going forward. One note about macro variables vs. FREQ. The scenario I set up was just to illustrate the macro issue with a generic dataset I had handy. I agree - I almost always use ODS OUTPUT of FREQs and PROC REPORT, but in this particular (real, not generic) scenario macro was a better option . . . hence my knowledge limitations were exposed!