BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

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.

8 REPLIES 8
santosh_pat69
Quartz | Level 8

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;

Reeza
Super User

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

SASAna
Quartz | Level 8
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

 

SASAna
Quartz | Level 8
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.
Reeza
Super User

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;
Reeza
Super User

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

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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
  • 8 replies
  • 2619 views
  • 0 likes
  • 4 in conversation