Summary: Macro design lesson #iw-value=3

Kevin,

Your are making things much to complex. Note that SUMM has no parameters. Hence the reader has no idea what is driving the process. Consider:

%macro summ (dslist=) ;

What is it doing? Looping through a list of data sets. Now what is in the loop? Well some kind of process, your stub is not revealing but it does write a log message, so let's make a macro:

%macro report (data=) ; %put processing &data ; %mend report ;

So what should SUMM look like?

%macro summ (dslist=) ; %local i ds ; %do i = 1 %to &sysmaxlong ; %let ds = %scan(&dslist,&i) ; %if %length(&ds) = 0 %then %goto endloop ; %report(data=&ds) %end ; %endloop: %mend summ ;

Test it

%summ(dslist = Prairie PrairieS LH LHS)

This produces the log

processing Prairie processing PrairieS processing LH processing LHS

Note how easy the test was, flying with no data. Now let's make some data

%macro mkdata(data=) ; data &data ; do obs = 1 to 5 ; output ; end ; run ; %mend mkdata ;

%summ(dslist = Prairie PrairieS LH LHS, mac=mkdata)

Hey, that's neat - SUMM makes it's own test data. Now what about that report? Well all I know is that you wanted to do something with SQL, possibly make a variable and report it to the log. So

%macro report (data=) ; select count(*) into :nobs from &data ; %let nobs = &nobs ; /* strip leading and trailing blanks */ %put &data has &nobs obs; %mend report ;

proc sql ; %summ(dslist = Prairie PrairieS LH LHS , mac=report) quit ;

Now let's get rid of the SUMM macro.

data w ; input data :$32. @@ ; cards ; Prairie PrairieS LH LHS ;

proc sql noprint ; select '%report(data='||trim(data)||')' into :maclist separated by " " from w ; &maclist quit ;

So we have solved you problem two different ways, shown you a lot about how to think and test in macro, but never even got close to know how to reference arrays of macro variables. Perhaps that suggests some rules:

Don't make an array of macro variables unless you have to.

If you macro variable name expressions get so complex that you have trouble with them, you are using the wrong approach.

If you have to ask something about macro quoting, you are either doing something that you shouldn't, or you haven worked with macro long enough to do it the way you are trying to do it.

Both solutions show the wisdom of Howard's suggestion to not put "PROC SQL" into the macro loop. More generally, move everything that you can out a loop to gain efficiency and reusability. The first step indicated how to work with lists, how to combine macros for easy testable solutions to problems, and how to pull the macro in a loop out of it so that it can be modified without changing the loop macro. The second solution showed that SQL is a great list maker and that an important list is the list of macro calls to execute. It also illustrates that data is usually the best source of lists.

Finally, your code and mine used COUNT(*) to get the number of observations, but remember, if you are really doing something in the SQL statement and not using the NOPRINT option, then &SQLOBS hold the number of observations written by the SELECT statement.

Beginner problems? Come to SESUG http://www.sesug.org/SESUG2007/index.htm Quentin McMullen and I will be doing a Sunday workshop on beginning macro.

Ian Whitlock ==============

Date: Thu, 5 Jul 2007 09:07:37 -0500 Reply-To: Kevin Morgan <kmorgan@GRAINSCANADA.GC.CA> Sender: "SAS(r) Discussion" From: Kevin Morgan <kmorgan@GRAINSCANADA.GC.CA> Subject: Macro Quoting Question Content-Type: text/plain; charset="us-ascii"

Hello:

I am relatively new to the world of macro programming in SAS and I have a small macro program here that I cannot get to work. What the program does is count the total number of observations in a number of different data-sets and then passes that value to a macro value.

%let S1 = Prairie; %let S2 = PrairieS; %let S3 = LH ; %let S4 = LHS ; %let S5 = VC ; %let S6 = VCS ; %let S7 = VR ; %let S8 = VRS ; %let S9 = NH ; %let S10 = NHS ; %let S11 = EA ; %let S12 = EAS ; %let S13 = BP ; %let S14 = BPS ; %let S15 = OTHER ; %let S16 = S_CG; %let S17 = SS_CG; %let S18 = S_CH; %let S19 = SS_CH; %let S20 = S_MJ; %let S21 = SS_MJ; %let S22 = S_SK; %let S23 = SS_SK; %let S24 = S_VC; %let S25 = SS_VC; %let S26 = S_WG; %let S27 = SS_WG; %let S28 = S_OTH;

OPtions mprint; %macro Summ; %do I=1 %to 27;

%put "Processing data set &&s&I."; proc sql noprint; select count(*) as count into: S_%unquote(&&s&I.) from &&S&I.; quit; %put "Macro &S_(%unquote(&&s&I.)) has &S_%unquote(&&s&I.) enteries"; %end; %mend summ; %summ ; Run;

The log of the first pass through the loop gives...

310 OPtions mprint; 311 %macro Summ; 312 %do I=1 %to 27; 313 %put "Processing data set &&s&I."; 314 proc sql noprint; 315 select count(*) as count as count into: S_%unquote(&&s&I.) from &&S&I.; 316 quit; 317 %put "Macro &S_(%unquote(&&s&I.)) has &S_%unquote(&&s&I.) enteries"; 318 %end; 319 %mend summ; 320 %summ ; Run; "Processing data set Prairie" MPRINT(SUMM): proc sql noprint; MPRINT(SUMM): select count(*) as count into: S_Prairie from Prairie; MPRINT(SUMM): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

WARNING: Apparent symbolic reference S_ not resolved. WARNING: Apparent symbolic reference S_ not resolved. WARNING: Apparent symbolic reference S_ not resolved. WARNING: Apparent symbolic reference S_ not resolved. WARNING: Apparent symbolic reference S_ not resolved. "Macro &S_(Prairie) has 504 enteries"

Of course the portion of the sql code {count into: S_%unquote(&&s&I.)}, that creates the macro name S_prairie is not working very well. I have played around with macro quoting functions as you can see but I just don't quite have it right. In the %put statement that follows proc sql, Macro &S_(Prairie) (not &S_Prairie) resolves to 504. Further when I try to use either of these macro values {i.e. S_(Prairie) or &S_Prairie} outside of the macro loop the macro is not resolved. Can anyone figure out where I went wrong with my qouting function?

Thank You,

Kevin