topic Exporting the data to xlsx in SAS Programming

Exporting the data to xlsx

My_SAS — Wed, 19 Dec 2012 09:33:30 GMT

Exporting the data :

As per the req i will create some dataset 10 to 1 crore and i wnat to export it to excel as xlsx is having a limit of
10,50,000(Approx 10 lac 50 thousand) and now i am doing manually and doing first obs and last obs and splitting the dataset to 10 lac and exporting
it how can i do it automatically and it should make the count and export to xls if the count is 65 lacs it should go to 7 excels of 10 lacs each

Re: Exporting the data to xlsx

esjackso — Wed, 19 Dec 2012 13:20:02 GMT

Something like this should work if I understand the problem correctly (10 lacs 50 thousand or 1 million 50 thousand rows [the actual limit in excel 2007 to 2010 I believe is 1,048,576 rows]), and this is for separate workbooks instead of worksheets but it could do either with some adjustment:

options mprint mlogic symbolgen;

%macro split(name,path);

Data _null_;

dsid = open("&name");

n = attrn(dsid,'nobs');

call symput('n',n);

run;

%put &n;

%do int = 1 %to &n %by 1000;

libname out excel "&path.\Out_&int..xlsx";

data _null_;

obs = &int + 1000;

call symput('obs',obs);

run;

%put &obs;

data out.OUT∫

set &name (firstobs=&int obs=&obs);

run;

libname out clear;

%end;

%mend split;

%split(temp,/*insert Path here*/);

Re: Exporting the data to xlsx

Haikuo — Wed, 19 Dec 2012 14:21:09 GMT

For an efficient and true dynamic approach, I prefer Hash plus excel libname engine: The following example is to split class into 5 students per sheet, and end up with 4 sheets.

libname test "c:\temp\test.xlsx";

data _null_;

declare hash h(ordered:'a');

h.definekey('n');

h.definedata('name','age','weight');

h.definedone();

do n=1 to 5 until (last);

set sashelp.class end=last;

h.add();

end;

h.output(dataset: cats('test.split',_n_));

run;

libname test clear;

Haikuo