I have large dataset (1million records) which i need to export to
excel from sas.

excel takes only 65000 records.

how can i get all the records into excel from sas?

can i get 1 million records in single sheet?

or do i need to get that into multiple sheets?

If your Excel version has a limit of 65000 records per sheet then is
this the limit and you have to store your 1M records in multiple
sheets.

May be Excel is just not the right product to store 1M records?

I believe that the newer version of Excel has one million rows.  Also, do 
you have to open the dataset after you export it?  If not, an CSV file would 
suffice.

"mahi"  wrote in message 
news:becfe95b-71c9-412a-991d-85f2187979e2@g19g2000yqi.googlegroups.com...

I have large dataset (1million records) which i need to export to
excel from sas.

excel takes only 65000 records.

how can i get all the records into excel from sas?

can i get 1 million records in single sheet?

or do i need to get that into multiple sheets? 

Excel 2007 will take 1,048,576 rows. 

If you're using older versions, then there's a limitation of 65K per sheet. Would recommend CSV - Because once you've exported so much data to Excel, it's pretty much unusable. 

On Jan 29, 11:02=A0pm, mahi <mehetrey.mahen...@gmail.com> wrote:
> I have large dataset (1million records) which i need to export to
> excel from sas.
>
> excel takes only 65000 records.
>
> how can i get all the records into excel from sas?
>
> can i get 1 million records in single sheet?
>
> or do i need to get that into multiple sheets?

Maybe you load it into Access and then query from within Excel.

Dirk

On Jan 29, 6:02=A0pm, mahi <mehetrey.mahen...@gmail.com> wrote:
> I have large dataset (1million records) which i need to export to
> excel from sas.
>
> excel takes only 65000 records.
>
> how can i get all the records into excel from sas?
>
> can i get 1 million records in single sheet?
>
> or do i need to get that into multiple sheets?

Hi there,

If you are using previous versions of Excel ie...< Excel 2007 then the
only option left is to write to multiple excel sheets...
or else if you are OK with other formats then put it to ,(comma) or a
| (pipe) delimited file and then open that with notepad
application....

if you still wanted to stick with Excel format...then split the
dataset to ~65000 rows and load it multiple work sheets....
Try with this macro example..

I've used ExcelXP tagsets that allow us to write to multiple worsheets
in a work book....
Inorder to try this example you will have to download this zip file
from SAS @ http://support.sas.com/rnd/base/ods/odsmarkup/tagsets_9.1.zip
and then find and extract excltags.tpl file and put it at your C:\ or
a location of your choice...In that case please update your location
at this statement %include "C:\excltags.tpl";

I hope this helps..Please let me know if you need any help....

options nosource nomprint nomlogic nosymbolgen;

/**
Usage of the Macro-
%splitdsnbyobs(DatasetName, No ofobservation to split
by,ExlFilename,ExlFilepath)
**/

/* creating a dataset with 100000 observations*/

%macro splitdsnbyobs(dsn,splitby,ExlFilename,ExlFilepath);

data _null_;
	set &dsn nobs=3Dnum;
	call symput('no_obs',num);
run;

%let no_obs=3D&no_obs; /*Get the number of observations in &dsn*/

%include "C:\excltags.tpl";
ods listing close;
ods tagsets.excelxp path=3D"&ExlFilepath" file=3D"&ExlFilename"
style=3Dprinter
      options( embedded_titles=3D'yes' embedded_footnotes=3D'yes'
suppress_bylines=3D'no');

/* keep the observations from firstobs=3D and obs=3D*/
%do i=3D1 %to %sysfunc(ceil(&no_obs/&splitby));
	data &dsn.&i.;
	set &dsn (firstobs=3D%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=3D
%sysfunc(ceil(%eval(&i * &splitby.))));
	run;

	ods tagsets.excelxp options(sheet_interval=3D'none'
sheet_name=3D"&dsn.&i.");
	proc print data=3D&dsn.&i.; run;
%end;

ods tagsets.excelxp close;
ods listing;

%mend splitdsnbyobs;

/* Eg. Create a Dsn with 1000000observations */
data loops;
do i=3D1 to 1000000;
	output;
end;
run;

/*Now call the macro to split the observations every 65000 records*/
%splitdsnbyobs(loops,65000,Example.xls,C:\);