LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2000, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: "Shelley S. Baxter" <Shelley.S.Baxter@KP.ORG>
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 */, /* 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 ; &outpref&k&insuff&j %end ; ;

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.

select ; %do j = 1 %to &datan ; when ( %eval((&i-1)*60000)<_n_<=%eval(&i*60000) ) output &outpref&k&outsuff&i; %end ; otherwise error ; end ;

As you can see I have largely suggest cosmetic changes, but they also provide more flexibility and better documentation.

Hope this helps. Ian Whitlock <>

-----Original Message----- From: Shelley S. Baxter [mailto:Shelley.S.Baxter@KP.ORG] Sent: Tuesday, June 27, 2000 5:51 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Macro of 84 revisited

John, Thanks for the reply! My first one in SAS-L!! Actually, the code I included worked fine for dividing and exporting to the 84 plus(depending on no. of obs in each of the 84 data sets) excel spread sheets. That was what the original BREAK macro was doing. The problem was to get a count of observations in *each* of the 84 datasets in the first place! I believe the solution is to getting them in is to do a do inside of a do: %do i=1 %to 84 and then a %do a %do i=1 %to &datan; where &datan is the number of datasets to be created out of each 84.

I made a mistake with the code I sent anyway--I was testing my routine on that dataset rel9uniques. I would want to be writing out to rel1&nidat&i thru rel84&indat&i from rel1&indat thru rel84&indat. Any ideas? Will this work? How do I deal with replacing rel9 with rel1 thru rel84???

%MACRO OBSCNT(dsn); %local nobs; %let nobs=.; %let dsnid=%sysfunc(open(&dsn)); %*If the open was successful get the; %*number of obs and close &dsn; %if &dsnid %then %do; %let nobs=%sysfunc(attrn(&dsnid,nlobs)); %let rc =%sysfunc(close(&dsnid)); %end; %else %do; %put Unable to open &dsn - %sysfunc(sysmsg()); %end; %*Return the number of observations; &nobs %MEND OBSCNT;

%MACRO BREAK(indat); %do I=1 %to 84; %let dsn=buckall.rel&i&indat; %let cnt=%obscnt(&dsn); %let datan=%sysevalf(&cnt/60000,ceil); data %do j= 1 %to &datan; testbuck.rel9&indat&j %end; ; set testbuck.rel9&indat; %do j= 1 %to &datan; %if &j>1 %then %do; else %end; if %eval((&i-1)*60000)<_n_<=%eval(&i*60000)then output testbuck.rel9&indat&i; %end; %do j=1 %to &datan; proc export data=testbuck.rel9&indat&j outfile="f:\lgr\testbucket\rel9&indat&j..xls"; run; %end; %end; run;

%MEND BREAK; options mprint; %break(uniques) %break(dups1) %break(dups2)

-----Original Message----- From: Wildenthal, John M. Sent: Tuesday, June 27, 2000 1:42 PM To: 'Shelley.S.Baxter@KP.ORG' Subject: Macro of 84

I'm not sure I understand what you are trying to do. Let me see if I have it right:

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 rather large.

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.

Sincerely yours, John M. Wildenthal Application Developer Viatel (409)691-4492 x4792 voice (409)268-4737 fax

Back to: Top of message | Previous page | Main SAS-L page