Date:         Mon, 22 Jun 2009 09:58:22 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: exporting a huge dataset into multiple excel sheets each of
              65000              rows
Content-Type: text/plain;charset=iso-8859-1

hi ... rather than EXPORT, you can try the EXCEL libname engine
this is "tweak" of the stuff shown in Jack Hamilton's paper ...

Creating Data-Driven Data Set Names in a Single Pass Using Hash Objects,
Proceedings of the 2007 SESUG Conference
http://analytics.ncsu.edu/sesug/2007/SD04.pdf



* fake data ... smaller than your data set, but shows how this method works;
data test;
retain name 'Richard Nixon' x y z 200;
do _n_=1 to 500000;
   output;
end;
run;

* write variable names to a macro variable;
proc sql noprint;
select quote(strip(name)) into :vars separated by ','
from dictionary.columns
where libname eq 'WORK' and memname eq 'TEST';
quit;

* the destination Excel file;
libname xcl 'd:\test.xls';

data _null_;
* define hash object;
dcl hash a ();
a.definekey('key');
a.definedata(&vars);
a.definedone();

* add observations to a hash object in increments of 65,000 observations;
do _n_=1 to 65000 until(lastrec);
   set test end=lastrec;
   key+1;
   a.add();
end;

* increment sheet number;
sheet+1;

* write contents of hash object to data set, delete hash object;
a.output(dataset:catt("xcl.sheet",sheet));
a.delete();
run;

libname xcl clear;


--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> Hi,
>
> I need to export a huge dataset having lets say 9060279 observations
> into multiple worksheets of an excel file,
>
> I needs to be done in such a way that each worksheet has 65000 rows
> till the last iteration and the last set of remaning observations in
> the last worksheet.
>
> so far I have this,
>
> %include "/home/vdharam/macros/delim.sas" ;
>
> libname outdata '/aodev/vik/data';
>
> %macro t;
>
> PROC PRINTTO LOG="/aodev/vik/krish/log/PUT.log" NEW;
>
> data _null_;
> set OUTDATA.ANALYST_MNGR_NAMES_ON_RAW_DAT nobs=cnt ;
> call symput('cnt',cnt);
> run;
>
> %put &cnt.;
>
> proc printto;
> run;
>
> do until(_n_<=&cnt.);
>
> data t1 t2 ;
> set OUTDATA.ANALYST_MNGR_NAMES_ON_RAW_DAT;
> by case_id;
> if _n_ < 65000 then
>    output t1;
> else output t2;
> run;
>
>
> filename myfile1 "/aodev/vik/krish/t1.csv" lrecl=1000;
> %makefile(dataset=t1,
>           filename=myfile1,
>           dlmr=",",
>           quote="yes",
>           header="yes",
>           label="yes" );
>
> filename myfile1 "/aodev/vik/krish/t2.csv" lrecl=1000;
> %makefile(dataset=t2,
>           filename=myfile1,
>           dlmr=",",
>           quote="yes",
>           header="yes",
>           label="yes" );
>
> %mend t;
> %t;
>
> ods tagsets.ExcelXP file='/aodev/vik/krish/body.xls';
>
> ods tagsets.ExcelXP options(sheet_name='A');
> proc print data=t1; run;
>
>
> ods tagsets.ExcelXP options(sheet_name='B');
> proc print data=t2; run;
>
>
> ods tagsets.ExcelXP close;
>
> Now the above code does the trick but i need to incorporate many
> manual changes like sheet name and breaking the rows into sets of
> 65000 rows.
>
> Can anyone please help me with the loop to break the huge dataset into
> sets of 65000 with counting itertions
> more effectively.
>
> Thanks
>