BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Badshaah
Obsidian | Level 7
Record are to high sas must divide data according to record size (observation) and produces export excel.
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I quite agree with @Kurt_Bremser, 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

11 REPLIES 11
Kurt_Bremser
Super User

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.

Badshaah
Obsidian | Level 7
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.
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I quite agree with @Kurt_Bremser, 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.

Badshaah
Obsidian | Level 7
Great... Whatever the thing...i manage.. But i was really curious and wondering that was even possible and how ?
Badshaah
Obsidian | Level 7
Thanks for support,.. Can we simplify this code..
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Badshaah
Obsidian | Level 7
Well thanks for support.
Badshaah
Obsidian | Level 7
Thanks for kind support, u nailed it

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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