|Date: ||Thu, 15 Sep 2005 08:12:12 -0700|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|Subject: ||Re: Macro Or Loop? (Both?)|
|Content-Type: ||text/plain; charset="iso-8859-1"|
Your Solution works perfectly!
And you are right it is a case where the "..the customer is always the
( I offered every other format possible but they want excel). More work
for them especially when the dates overlap files... but I warned them).
> The MIN and MAX values in the initial program reflect the date extremes
> from the entire input dataset. If you want each "bucket" of the Excel
> files to reflect the dates more accurately, you could do something like
> the following - it takes the min and max dates for each group of
> observations. Using the SUFFIX macro variable in the file name
> prevents overwriting if more than one group of observations has similar
> min and max values.
> Why anyone would want to handle this amount of data in Excel is beyond
> me, but as we all know from our days as retail trainees at Sears: "the
> customer may not always be right, but the customer is always the
> data test;
> do i = 1 to 11000;
> eventDay = '01jan1950'd + i;
> %macro cre8xls(data=, file=, size=5000);
> proc sql noprint; /* assume it's a WORK dataset.
> If not, tweak the WHERE clause */
> select nobs
> into :n
> from dictionary.tables
> where memname="%upcase(&data.)" & libname="WORK"
> %let suffix = 0;
> %do i = 1 %to &n. %by &size.;
> %let suffix = %eval(&suffix. + 1);
> %let endObs = %eval(&i.+ &size.-1);
> proc sql noprint;
> select min(EventDay) format=date9., max(EventDay)
> into :min, :max
> from &data.(firstobs=&i. obs=&endobs.);
> %put Iteration &suffix. - obs &i. - &endObs. Min: &min.
> Max: &max;
> proc export data=&data.(firstobs=&i. obs=&endObs.)
> OUTFILE="c:\temp\&file. &suffix. from &min. to
> DBMS=EXCEL2000 REPLACE;
> options mprint;
> %cre8xls(data=test, file=testxls, size=5000);