Date:         Wed, 14 Jan 2004 22:30:25 -0500
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: Conditional export to Excel

Others have provided solutions (or good starts on solutions) to the problem
as stated.

I'm wondering why you want to do this. It does not seem that the results
will be very useful.

Instead, I would export the data to MS Access, then set up the Access table
as a data source for Excel, with some user-configurable filters. That way
different subsets can be materialized in Excel, as needed, and queries
against the whole set can be done in Access.

On Tue, 6 Jan 2004 10:18:36 -0500, Debbie Cooper
<debbie.cooper@STEVENSONCOMPANY.COM> wrote:

>I have a large file containing 450,000 records but only two variables.  I'd
>like to export the file to excel but of course the entire file won't
export.
>The first field in the file is a text field and the file is sorted
>alphabetically based on that field.  Is there a way to conditionally export
>the file based on record count and the letter of the alphabet?  So, for
>example, if all of the "a's" totalled less than say 40000 records I would
>want to export just the "a's".  If the "d's" and "e's" ended up being less
>than say 40000 records then I'd want to export both of those to another
>sheet.  The 40000 is an arbitrary cutoff but I'd also like to cutoff each
>sheet based on letter of the alphabet.  Here's some code I've come up with
>so far but I'd like to make this a little more automated.  I started to use
>macro variables to track the number of obs but the code is still too
>cumbersome.  This code exports a small file but the largest file is 450000
>records and will need to be segmented a lot more.
>
>data testapp1;
>        set testapp;
>        if substr(rwbr,1,1) < "I";
>        call symput('numobs1',_n_);
>run;
>data testapp2;
>        set testapp;
>        if substr(rwbr,1,1) >= "I";
>        call symput('numobs2',(_n_ - &numobs1));
>run;
>
>**** OPEN EXCEL AND POPULATE WORKSHEETS  ******;
>FILENAME file1 DDE "EXCEL|Sheet1!r1c1:r65000c2" notab;
>data _null_;
>        file file1;
>        set testapp1;
>        put rwbr '09'x br ;
>run;
>FILENAME file2 DDE "EXCEL|Sheet2!r1c1:r65000c2" notab;
>data _null_;
>        file file2;
>        set testapp2;
>        put rwbr '09'x br ;
>run;
>
>Thanks,
>Debbie