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.
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;
@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).
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.
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;
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
Excel currently has a limitation of 1048576 rows as of Excel 2013. Please clarify your question.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.