DATA Step, Macro, Functions and more

ODS Excel APPEND

Reply
Frequent Contributor
Posts: 95

ODS Excel APPEND

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.

Contributor
Posts: 66

Re: ODS Excel APPEND

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;

Super User
Posts: 19,876

Re: ODS Excel APPEND


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).

Frequent Contributor
Posts: 95

Re: ODS Excel APPEND

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.
Super User
Super User
Posts: 7,997

Re: ODS Excel APPEND

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.

 

 

Frequent Contributor
Posts: 95

Re: ODS Excel APPEND

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.
Super User
Posts: 19,876

Re: ODS Excel APPEND

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;
Super User
Posts: 19,876

Re: ODS Excel APPEND

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

 

 

Super User
Super User
Posts: 7,997

Re: ODS Excel APPEND

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

Ask a Question
Discussion stats
  • 8 replies
  • 491 views
  • 0 likes
  • 4 in conversation