BookmarkSubscribeRSS Feed
fabdu92
Obsidian | Level 7

Hi all,

 

As you know, Excel is limited by its number of rows. According to this, I have a user requirement telling that: "The maximum number of records is 1.048,576 rows"

 

I have to export a table in an Excel sheet with famous PROC EXPORT

 

I want to perform this requirement by exporting the table in the Excel sheet if the number of rows is less, or else displaying a error in the Excel sheet telling something like "Too much data: the number of records is N", and replace N by the number.

 

But honestly, I am not an expert in SAS, even more a beginner than expert so I really don't know if there is a way to do it.

Can you help me?

 

Thanks 

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, first off is the usual text.  Why are you using Excel - unstrcutured, uncontrolled, very bad format for data transport - as a data transport format?  Not only will you hit certain restrictions on size with it, Excel has lots of "features" which really make it a bad choice.  Also its far more difficult to import/qc.  I really would use any other available format - CSV, XML, dataset, delimited, json etc.  All are far better.

 

As for your code you could do (although if your not familiar with SAS, this is a bit more advanced):

data _null_;
  set sashelp.vtable (where=(libname="L1" and memname="D1"));
  do i=1 to (nobs / 1000);
    call execute('data tmp; set l1.d1; if '||strip(put(i*1000,best.))||' <=_n_ < '||strip(put((i+1)*1000),best.))||' then output; run;');
    call execute('proc export outfile="c:\outputfile'||strip(put(i,best.))||'.xlsx" data=tmp; run;'); 
  end;
run;

What this does is generate a datastep and proc export for each block of observations of 1000 obs.  Note change L1 and D1 to your lib and dataset, and change 1000 to number of obs.

However I still really recommned not using Excel.

Reeza
Super User

You want functionality like this

 

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#p011imau3tm4jen1u...

 

in this case of 0, it prints an error message. You can change the proc print to an export and the message as required. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 7344 views
  • 0 likes
  • 3 in conversation