topic Re: Proc export in SAS Programming

Proc export

singhsahab — Wed, 03 Feb 2021 13:52:38 GMT

Hi All,

I do have more then 3 million record in my table. how i can export it in excel sheet. As we know excel is supporting only 1048576 number of rows. i will look forward for your response.

Thank you in advance .

Regards,

S.S

Re: Proc export

Tom — Wed, 03 Feb 2021 13:59:02 GMT

@singhsahab wrote:

Hi All,

I do have more then 3 million record in my table. how i can export it in excel sheet. As we know excel is supporting only 1048576 number of rows. i will look forward for your response.

Thank you in advance .

Regards,

S.S

Seems silly to force something into a format that cannot handle it. You need to split the data. If you use the XLSX engine then it is just like splitting into separate datasets (only the performance will be much worse).

libname out xlsx 'way_too_large.xlsx';
data out.one;
  set have (obs=1000000);
run;
data out.two;
  set have (firstobs=1000001 obs=2000000);
run;
data out.three
  set have (firstobs=2000001 obs=3000000);
run;
...

Re: Proc export

ballardw — Wed, 03 Feb 2021 15:14:19 GMT

Just how do you expect to actually use 3 million records in a program that only wants to support 1 million?????

If this is one of those "the boss says it needs to be in Excel" with a very specific reason I would then ask the boss "What do you want to do with the 2 million records that Excel won't support?"

I've to fight that particular fight off and on since the time Excel only supported 64K records.