DATA Step, Macro, Functions and more

How divide dataset into multiple sheet on same excle

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How divide dataset into multiple sheet on same excle

Record are to high sas must divide data according to record size (observation) and produces export excel.

Accepted Solutions
Solution
‎12-02-2016 04:36 AM
Super User
Super User
Posts: 7,942

Re: How divide dataset into multiple sheet on same excle

I quite agree with @KurtBremser, you could not pick a worse format for transporting data.  You will encounter all kinds of issues.

 

Generally speaking you have several options to export data to Excel:
ods tagsets.excelxp - this will generate XML which Excel can read in easily, its not real Excel though

proc export - generates a binary Excel file

libname excel - not really used much, but access xlsx directly

 

Now you could code as (using proc export);

data _null_;
  set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS"));
  do i=1 to nobs / 10000;
    call execute('data temp; set yourlib.yourds; if '||strip(put((i-1)*10000,best.))||' <= _n_ < '||strip(put(i*10000,best.))||' then output; run;');
    call execute('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="Sheet'||strip(put(i,best.))||'"; run;');
  end;
run;

What this does is loop from 1 to nobs/10000 times and for each loop generates a datastep which filters records for that group, then proc export code to export that dataset to a sheet.

 

However, one last time, do everything possible to avoid using Excel, it is not a useable data format in any sense, and most people will refuse it.

View solution in original post


All Replies
Super User
Posts: 7,771

Re: How divide dataset into multiple sheet on same excle

Simple tip:

 

DO NOT USE THE EXCEL FILE FORMAT FOR DATA TRANSFER!!

 

A csv file does not have the limitations of the Excel kludge, is readable with a text editor for control, and works reliably with lots of applications.

 

And if you have data that exceeds the limits for a spreadsheet (which are way too high anyway, as nobody can keep control of a spreadsheet with > 1000 lines), why are you trying to "use" it in Excel? You have SAS at your hands, which is more powerful than Excel by orders of magnitude.

 

If you want to use Excel for nice displays, that's OK, but do the heavy work in SAS and only hand the compacted results over to Excel.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: How divide dataset into multiple sheet on same excle

Excel is a requirement to my client ...so how automate the code ? So we don't need to think about size(no. Of obs) . Automatically record's get divide and store in different sheets of same excel.
Super User
Posts: 7,771

Re: How divide dataset into multiple sheet on same excle

Tell your client that the data is too large for Excel, and deliver the data in a csv. When your client tries to open that in Excel, they will hopefully realize their foolishness.

Everything else is unethical, IMO.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: How divide dataset into multiple sheet on same excle

Posted in reply to KurtBremser

Depends on two things really - firstly the experience of the person getting the Excel file of course.  If they haven't hit some of the walls provided by the format, then they will know no different.  However there is a secondary reason as well.  Excel's popularity stems from the very simple and easy to use Front End, so if your a Front End user then you will love it - its why management use it, its simple and can produce colorful nonsense for them to mull over for hours on end before dropping whole departments.  These users do not see the back end of it and hence have no issues.  It avoids all that nastiness of setting up entry screens, creating edit checks and audit trails and such like, in favour of moving all that responsiblity from them and their database, to the end user of the data.

This is why now, rather than try to explain it, any time anyone sends me an Excel file, or states they will use it, I simply increase the budgetary need accordingly, I am sure most groups are able to understand financial implications.

And of course, slight disclaimer, there are some who have no option than to use it, however with a direct CSV exporter in Excel, or some knowledge of VBA this shouldn't be an excuse.

Solution
‎12-02-2016 04:36 AM
Super User
Super User
Posts: 7,942

Re: How divide dataset into multiple sheet on same excle

I quite agree with @KurtBremser, you could not pick a worse format for transporting data.  You will encounter all kinds of issues.

 

Generally speaking you have several options to export data to Excel:
ods tagsets.excelxp - this will generate XML which Excel can read in easily, its not real Excel though

proc export - generates a binary Excel file

libname excel - not really used much, but access xlsx directly

 

Now you could code as (using proc export);

data _null_;
  set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS"));
  do i=1 to nobs / 10000;
    call execute('data temp; set yourlib.yourds; if '||strip(put((i-1)*10000,best.))||' <= _n_ < '||strip(put(i*10000,best.))||' then output; run;');
    call execute('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="Sheet'||strip(put(i,best.))||'"; run;');
  end;
run;

What this does is loop from 1 to nobs/10000 times and for each loop generates a datastep which filters records for that group, then proc export code to export that dataset to a sheet.

 

However, one last time, do everything possible to avoid using Excel, it is not a useable data format in any sense, and most people will refuse it.

Contributor
Posts: 34

Re: How divide dataset into multiple sheet on same excle

Great... Whatever the thing...i manage.. But i was really curious and wondering that was even possible and how ?
Contributor
Posts: 34

Re: How divide dataset into multiple sheet on same excle

Thanks for support,.. Can we simplify this code..
Super User
Posts: 7,771

Re: How divide dataset into multiple sheet on same excle

You have a dumb requirement, and dumb requirements tend to end up as dumb (unnecessarily complicated) code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: How divide dataset into multiple sheet on same excle

How do you mean simplify?  You could use cat() function if you don't like ||, but not sure how simpler you could make it as this only contains the necessary parts:

data _null_;
  set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS"));
  do i=1 to nobs / 10000;
low=strip(put((i-1)*10000,best.));
high=strip(put(i*10000,best.));
sheet=cats("Sheet",put(i,best.)); call execute(cat('data temp; set yourlib.yourds; if ',low,' <= _n_ < ',high,' then output; run;'); call execute(cat('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="',sheet,'"; run;'); end; run;

 Maybe that looks simpler?

Contributor
Posts: 34

Re: How divide dataset into multiple sheet on same excle

Well thanks for support.
Contributor
Posts: 34

Re: How divide dataset into multiple sheet on same excle

Thanks for kind support, u nailed it
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 311 views
  • 10 likes
  • 3 in conversation