Date: Wed, 28 Jun 2000 10:12:09 -0400
Reply-To: Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject: Re: Macro of 84 revisited
Content-Type: text/plain; charset="iso-8859-1"
I strongly prefer keyword parameters with default values, so I would have
%macro break ( INNDSN=84, /* number of input data sets */
INPREF=lib.in, /* prefix for input data set */
INSUFF=in, /* suffix for input data set */
OUTPREF=lib.out, /* prefix for output data set */
OUTSUFF=out, /* suffix for output data set */
OUTLIM=60000 /* limit on output data set */
This interface allows the user to choose his libraries and naming schemes.
It makes the macro easy to test and provide more flexibility.
I would be careful to add %LOCAL statements for all variables in particular
I, J, and K. I like
to use it for documentation as follows.
%local dsn /* constructed input data set name */
i /* index for main loop over input data sets */
j /* temp index for other loops */
k /* holds &i or null value for constructing dsn */
cnt /* return value from %OBSCNT */
datan /* number of output data set for one input set */
Your macro variable DSN would be
%let dsn = &inpref&i&insuff ;
Your DATA statement could be
%if &inndsn = 1 %then %let k = ;
%else %let k = &i ;
data %do j = 1 %to &datan ;
This would allow one to have no number separating &OUTPREF and &OUTSUFF when
none was needed, yet
also allow a numbering scheme when it is needed.
I would prefer the SELECT block to decide where data is output instead of
IF/ELSE, since I think
it easier to read.
%do j = 1 %to &datan ;
when ( %eval((&i-1)*60000)<_n_<=%eval(&i*60000) )
otherwise error ;
As you can see I have largely suggest cosmetic changes, but they also
provide more flexibility and better documentation.
Hope this helps.
Ian Whitlock <email@example.com>
From: Shelley S. Baxter [mailto:Shelley.S.Baxter@KP.ORG]
Sent: Tuesday, June 27, 2000 5:51 PM
Subject: Macro of 84 revisited
Thanks for the reply! My first one in SAS-L!!
Actually, the code I included worked fine for dividing and exporting to the
plus(depending on no. of obs in each of the 84 data sets) excel spread
That was what the original BREAK macro was doing.
The problem was to get a count of observations in *each* of the 84 datasets
the first place!
I believe the solution is to getting them in is to do a do inside of a do:
i=1 %to 84 and then a %do a %do i=1 %to &datan; where &datan is the number
datasets to be created out of each 84.
I made a mistake with the code I sent anyway--I was testing my routine on
dataset rel9uniques. I would want to be writing out to rel1&nidat&i thru
rel84&indat&i from rel1&indat thru rel84&indat.
Will this work? How do I deal with replacing rel9 with rel1 thru rel84???
%*If the open was successful get the;
%*number of obs and close &dsn;
%if &dsnid %then %do;
%let rc =%sysfunc(close(&dsnid));
%put Unable to open &dsn - %sysfunc(sysmsg());
%*Return the number of observations;
%do I=1 %to 84;
%do j= 1 %to &datan;
%do j= 1 %to &datan;
%if &j>1 %then %do;
%do j=1 %to &datan;
proc export data=testbuck.rel9&indat&j
From: Wildenthal, John M.
Sent: Tuesday, June 27, 2000 1:42 PM
Subject: Macro of 84
I'm not sure I understand what you are trying to do. Let me see if I have
You have 84 (or any arbitrary number) of datasets. You are (probably) going
to use PROC EXPORT to put them into Excel spreadsheets. Each worksheet
holds a maximum of 2^16 observations, so you need to break (or rearrange)
your 84 datasets so that you won't try writing more than 2^16 observations
to a single worksheet.
There are several solutions. One is using DDE to write to more than one
worksheet. Heck, you could even have all the data in a single workbook that
way, but it will take a bit more coding and your workbook could end up
What I do not know is whether the data has structure represented by being in
the 84 files - does it matter if data from file 2 is appended onto the end
of file 1 when filling out the spreadsheet? Or if the data from different
input files is mixed in any way? If there are limits on that, then SAS-L
helpers will need to know that.
John M. Wildenthal
(409)691-4492 x4792 voice