Re: Macro Quoting Question

Ian,

Thanks for the advice. You are right I did totally make things more
complex than they needed to be. In fact I made it so complex that I
finally gave up and coded each proc SQL step manually, vowing that I
would figure out macro programming well enough to program this macro on
my own, someday.

I have only recently started programming in SAS with macros and I
fancied myself as getting pretty good at it. Unfortunately, I realize
now what I have gotten good at is %do loop processing while ignoring
some of the other important macro functions, or rules for processing.

As an interesting side point Toby Dunn pointed me towards list based
macro processing and recommended a Global Forum Paper Names,Names,Names,
- Make me a list which was of course written by you!!! I read through it
a couple of times on my lunch hour today and I have to say I was pretty
lost. I have decided that my evening reading will be getting started
with macro also by you.

I would love to come out to Hilton head for the SESUG and see your demo
on macros. Unfortunately my boss knows that my travel request would be a
shallow attempt to get out play golf at Harbour Town. I do hope to meet
up with you at the next global forum.

Regards,

Kevin Morgan


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ian
Whitlock
Sent: Thursday, July 05, 2007 4:10 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Macro Quoting Question

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

On Jul 6, 1:33 pm, kmor...@GRAINSCANADA.GC.CA (Kevin Morgan) wrote:
> Ian,
>
> Thanks for the advice. You are right I did totally make things more
> complex than they needed to be.

Macros tend to make the simplest things complicated.

Here's some non-macro code you could modify to get results similar to
what you were looking for. I have tested it on some SAS help tables,
you can subsitute your own. It creates a dataset for a result, if you
really need the results as SAS macro variables use the call symputx
statement in a subsequent step, or use the SQL into: statement

See also http://www2.sas.com/proceedings/sugi30/070-30.pdf

data memnames;
length memname $32; infile cards; input memname;
cards;
AIR
CLASS
SHOES
;
run;

proc sql;
create table observations as
select libname, memname, nobs
from dictionary.tables
where libname='SASHELP' and memname in select * from memnames;
quit;