topic Re: ODS Excel APPEND in SAS Programming

ODS Excel APPEND

SASAna — Wed, 25 May 2016 21:03:56 GMT

As ODS Excel has limitation of 1200 records per sheet, i wanted to try option of appending the dataset/proc report in the same sheet.

Currently it is writing it in second tab of Excel. IS there a way to append?

Requirment is to get the ODS EXCEL o/p only.

Re: ODS Excel APPEND

santosh_pat69 — Wed, 25 May 2016 21:12:42 GMT

libname xl Excel "&SASForum.\Datasets\Append.xls";

/* Create the Append.xls workbook with a sheet named myData$ and a named range
called myData */
data xl.myData;
x = 1;
run;

%macro append;
%do i = 2 %to 5;
data have&i;

x = &i;

run;

proc sql;
/* Save the contents of the Excel table */
create table have as

select * from xl.myData;
/* Erase the contents of the table in the workbook, Excel doesn't allow you
to overwrite an existing table */

drop table xl.myData;
/* Recreate the table with your added lines. It will replace the old version
at the same place, with the same cell formatting */

          create table xl.myData as
               select * from have
               union all

select * from have&i;

quit;
%end;
%mend append;

%append;

libname xl clear;

Re: ODS Excel APPEND

Reeza — Wed, 25 May 2016 22:00:01 GMT

@SASAna wrote:

As ODS Excel has limitation of 1200 records per sheet, i wanted to try option of appending the dataset/proc report in the same sheet.

Can you reference that limitiation?

I just exported 4000K records?

Are you talking about columns/variables?

ODS EXCEL does support multisheet so you can print to multiple sheets if necessary.

Also, what version of SAS do you have, including release (ie TS1M2).

Re: ODS Excel APPEND

RW9 — Thu, 26 May 2016 08:36:21 GMT

Excel currently has a limitation of 1048576 rows as of Excel 2013. Please clarify your question.

Re: ODS Excel APPEND

SASAna — Thu, 26 May 2016 13:52:44 GMT

o/p has 400 columns and job is failing due to insufficient memory error due to ODS excel limitation of 1200 rows only. Testing o/p has 35,000 records.

Re: ODS Excel APPEND

RW9 — Thu, 26 May 2016 13:57:52 GMT

How are you running this, is it through SAS9.x, Enterprise Guide, University Edition etc.?

What does "o/p" mean?

Post the full log surrouding the issue.

Post the code used which generates the error.

Why do you have 400 columns, even 40 is a large amount.

None of what is posted there should be a limitation from Excel.

Re: ODS Excel APPEND

SASAna — Thu, 26 May 2016 15:37:31 GMT

I am running the SAS job through UNIX and have 9.2 version of SAS.
I am exporting the a large amount of data around 50,000 records and 400 colums (attributes) . Currently we have HTML method of exporting which works fine, but new requierment is to change it to ODS EXCEL.

Re: ODS Excel APPEND

Reeza — Thu, 26 May 2016 15:42:48 GMT

ODS Excel is only available in SAS 9.4+, not SAS 9.2

SAS 9.2 has support for ODS tagsets.excelxp but this destination does not support graphics or appending to a file. Additionally, similar to ODS HTML it does not generate a native excel file, but an XML file.

A simple PROC PRINT wll work using excelxp.

ods tagsets.excelxp file="my_sample_file.xml";

proc print data=have;
run;

ods tagsets.excelxp close;

Re: ODS Excel APPEND

Reeza — Thu, 26 May 2016 15:47:21 GMT

I don't know why you have a requirement of ODS EXCEL, is the actual requirement to generate a native excel file? Are you formatting your output in some manner that requires ODS EXCEL?

Another option is this:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export