Creating and Using Macro within Same Step

[sasser27]

Hello, I'm trying to utilize the macro below to prevent typing out this full sql query many times throughout the code. The into :&count is causing issues. Although it doesn't show an error, later when I call salescount, it's clear the macro was not assigned properly "Apparent symbolic reference salescount was not resolved." Is there a way to fix the macro or will I have to go back to writing each proc sql 

%macro counts (variable1, count, dataname);

proc sql;
select count(distinct &variable1) into :&count trimmed
from &dataname;
quit;
%mend counts;

%counts(userid, salescount, customers)

[antonbcristina]

Hi @sasser27, there's a few ways to proceed here. You could provide a sample customers dataset (just a few observations) for us to test your macro or you could test out the SQL query by itself to see if the result is as expected before encapsulating it in a macro. 

I've tested out the macro with a simple customers table that I created:

data customers;
	input userid;
	format userid z3.;
datalines;
001
002
003
;
run;

and the macro worked and &salescount returned the number 3 in the log, as expected. 

80   %put &salescount;
3

Global system options MPRINT and SYMBOLGEN are also useful when working with macros to be able to trace what a macro is doing: 

options mprint symbolgen;

[Cynthia_sas · Accepted solution]

Hi,

  The main issue you're having is that the macro variable is in the local scope, which means that after the macro program executes, that variable is "cleaned up" with the rest of the local macro variables used within the macro program. So in order to be able to use your macro variable from the PROC  SQL after the macro ends, you need to declare at least the &count macro variable value as a GLOBAL macro variable:

Hope this helps,

Cynthia

[ballardw]

Your example is incomplete because it does not show anything that would generate the "Apparent symbolic reference salescount was not resolved." message where you attempt to use the Salescount variable.

Part of the issue is that the value of &count in the macro is local in scope, that means it only exists inside the macro Counts and code created/used there.

The fix would be to make the macro variable global:

%macro counts (variable1, count, dataname);
%global &count;
proc sql;
select count(distinct &variable1) into :&count trimmed
from &dataname;
quit;
%mend counts;

And an example that uses the macro with a data set you can test and uses the created macro variable holding the count:

%counts (name,namecount,sashelp.class);

%put Namecount is "&namecount";

Which does return the expected value of 19

[Tom]

In your macro call you asked the macro to put the count into a macro variable named SALESCOUNT.  If you want to use SALESCOUNT after the macro ends then make sure to define it BEFORE calling the macro.

%let salescount=unknown;
%counts(userid, salescount, customers)
%put &=salescount;

You named the parameter COUNT, but I would think a name like MVAR or MVARNAME would have been better. It would make it clearer how the parameter is going to be used.  You can make the macro smart enough to force the macro variable into the global symbol table when it does not already exist.

%macro counts (varname, mvarname, dsname);
%if not %symexist(&mvarname) %then %global &mvarname;
proc sql;
select count(distinct &varname) format=32. into :&mvarname trimmed
from &dsname;
quit;
%mend counts;

%counts(dsname=customers,varname=userid, mvarname=salescount)