I am writing a macro program in which I want to be able to call the grand mean of a variable to use as a reference line. (In my example code here, I'm using a statement that will go into the macro, which will be called by CALL EXECUTE in order to make multiple charts.)
%let analysisvar = MPG_City; proc sql ; select mean(&analysisvar) into :M_&&analysisvar from sashelp.cars; quit; %put &M_&ANALYSISVAR; /*This does NOT resolve to the mean.*/ %put &&M_&ANALYSISVAR; /*This DOES resolve to the mean but why?*/
I thought what is happening is that the PROC SQL statement is creating a macro variable &M_&AnalysisVar, but that does not appear to be the case.
I would have expected %put &M_&AnalysisVar to resolve to the actual mean, but instead it resolves to &M_MPG_City. I'm trying to wrap my mind around why &&M_AnalysisVar resolves to the numeric value I want. I was wondering if someone knew the steps in which the ampersands are being resolved in PROC SQL in this way of assigning a value to a macro.
Just pretend you are the macro processor. Your whole reason for living is to hunt out % and & and replace text with other text. So let's step through your program.
The macro processors ears perk up right on the first character, %, so it checks out that text and discovers a %LET statement. It stores the text MPG_City into the macro variable analysisvar (creating analysisvar if it did not already exist) but emits NO text from that statement.
Next it sees some text with an &. It emits the text up until the &.
proc sql ; select mean(
and checks out want follows the &. It sees a string that looks like the name of macro variable it knows about so it replaces &analysisvar with MPG_City and emits that text to SAS.
MPG_City
Then it seems more text followed by & so it emits that text.
) into :M_
And starts to check out what it sees. I sees two & next to each other so it replaces them with a single & and reminds itself it needs check this string out again when it reaches the end.
Then it sees more text followed by a space (or end of line) so it knows it has hit the end of the token which is now &analysisvar which it replaces with MPG_City and emits.
MPG_City
The it sees more text until there is a % so it emits that text.
from sashelp.cars;
quit;
At this point SAS has seen the whole SQL step and has run it.
The macro processor then recognizes the %PUT statement and so starts checking out what you want it to write to the LOG.
It then sees & and starts checking if this is a macro variable reference. But instead if just finds the token &M_ so it issues a warning that it doesn't know anything about this M_ macro variable and writes the &M_ to the log.
Then is sees & and this time recognizes our old friend and writes its value to the log.
For the second %PUT statement when it sees the double & it again replaces them with a single & and reminds itself to re-review this token when it gets to the end. It then sees the plain text M_ and remembers it. Then it sees the reference to our old friend analysisvar again and replaces it. Then it reaches the ; so it knows it has reached the end the token and rescans what it has which is now &M_MPG_City. This looks like a reference to a macro variable, and it should be found since the SQL code that it just gave to SAS to run created it. So the %PUT statement writes the value of M_MPG_City to the log.
Indirect referencing, with the multiple &&&&& type calls is basically the leftmost & (or multiples) hold off resolving until the ones to the right resolve.
The question is why did you use the
into :M_&&analysisvar
in the first place
When I run your code the log shows:
11 %let analysisvar = MPG_City; 12 13 proc sql ; 14 select mean(&analysisvar) into :M_&&analysisvar 15 from sashelp.cars; 16 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Apparent symbolic reference M_ not resolved.
18 %put &M_&ANALYSISVAR; /*This does NOT resolve to the mean.*/
&M_MPG_City
That particular warning basically means you have attempted to use a macro variable that doesn't exist, which is why it shows the &M_MPG_City in the log. You have implied that you have two macro variables, M_ and Analysisvar.
When you use the
put &&M_&ANALYSISVAR;
The first & holds off resolving and generates the equivalent of &M_MPG_CITY, which is the macro variable created.
You can see which macro variables you have defined currently in the scope you execute it with and their value by using
%put _user_;
To reference a macro variable you only need one &. The happy accident is that using two also worked.
Using the SYMBOLGEN option can help you see what is happening.
337 %let analysisvar=MPG_City; 338 339 options symbolgen; 340 %put M_&analysisvar ; SYMBOLGEN: Macro variable ANALYSISVAR resolves to MPG_City M_MPG_City 341 %put M_&&analysisvar ; SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable ANALYSISVAR resolves to MPG_City M_MPG_City
Just pretend you are the macro processor. Your whole reason for living is to hunt out % and & and replace text with other text. So let's step through your program.
The macro processors ears perk up right on the first character, %, so it checks out that text and discovers a %LET statement. It stores the text MPG_City into the macro variable analysisvar (creating analysisvar if it did not already exist) but emits NO text from that statement.
Next it sees some text with an &. It emits the text up until the &.
proc sql ; select mean(
and checks out want follows the &. It sees a string that looks like the name of macro variable it knows about so it replaces &analysisvar with MPG_City and emits that text to SAS.
MPG_City
Then it seems more text followed by & so it emits that text.
) into :M_
And starts to check out what it sees. I sees two & next to each other so it replaces them with a single & and reminds itself it needs check this string out again when it reaches the end.
Then it sees more text followed by a space (or end of line) so it knows it has hit the end of the token which is now &analysisvar which it replaces with MPG_City and emits.
MPG_City
The it sees more text until there is a % so it emits that text.
from sashelp.cars;
quit;
At this point SAS has seen the whole SQL step and has run it.
The macro processor then recognizes the %PUT statement and so starts checking out what you want it to write to the LOG.
It then sees & and starts checking if this is a macro variable reference. But instead if just finds the token &M_ so it issues a warning that it doesn't know anything about this M_ macro variable and writes the &M_ to the log.
Then is sees & and this time recognizes our old friend and writes its value to the log.
For the second %PUT statement when it sees the double & it again replaces them with a single & and reminds itself to re-review this token when it gets to the end. It then sees the plain text M_ and remembers it. Then it sees the reference to our old friend analysisvar again and replaces it. Then it reaches the ; so it knows it has reached the end the token and rescans what it has which is now &M_MPG_City. This looks like a reference to a macro variable, and it should be found since the SQL code that it just gave to SAS to run created it. So the %PUT statement writes the value of M_MPG_City to the log.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.