The SAS Output Delivery System and reporting techniques

SAS ODS EXCEL OUTPUT

Reply
Occasional Contributor
Posts: 5

SAS ODS EXCEL OUTPUT

Is there any option in ods Excel to print the tables in the same sheet without a blank row in between(similar to Skip_space in ODS TAGSETS.EXCELXP). Or any other work around solution?

Trusted Advisor
Posts: 1,500

Re: SAS ODS EXCEL OUTPUT

Occasional Contributor
Posts: 5

Re: SAS ODS EXCEL OUTPUT

I still get a blank row between with sheet_interval="none" option.

blank row.PNG

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: SAS ODS EXCEL OUTPUT

So you want the variable names to appear directly under the previous outputs data?  I don't think there is an option for this.  You could try turning row headers/titles off and see what happens - options can be found here:

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...

 

Or maybe you want all the data to appear under one set of variable names?  If so set the data together before outputting.

Occasional Contributor
Posts: 5

Re: SAS ODS EXCEL OUTPUT

I dont want the variable names to be repeated for each dataset.

There are almost 100K records in my dataset.When I am trying to print it using ODS excel at one go I am getting below error.

"ERROR: The SAS System stopped processing this step because of insufficient memory". Changing MEMSIZE is not an option for me.

 

Hence I splitted the dataset into 10 smaller ones to print it separately. But I am getting blank row after every 10000 rows.

Is there any other way other than splitting or changing memsize to resolve this issue.

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: SAS ODS EXCEL OUTPUT

You should be able to output and use a 100k records pretty easily, SAS can handle far more than that.  What software are you using?  Is it SAS University Edition?  There may be limitations.  If its not, then check how much memory you have allocated, although that doesn't sound like many records for memory purpose.  Why are you outputting 100k records to Excel for?  Nobody will ever review that amount of data.  Is it for a data transfer, use CSV, this can be opened in Excel easily, and it is a far more robust format for transfering data.  

SAS Employee
Posts: 88

Re: SAS ODS EXCEL OUTPUT

The row can also be hidden using the HIDDEN_ROWS= suboption like the below.

 

ods excel file="c:\temp.xlsx" options( sheet_interval="none" hidden_rows="21");

proc print data=sashelp.class;

run;

proc print data=sashelp.class;

run;

ods excel close;

Ask a Question
Discussion stats
  • 6 replies
  • 192 views
  • 1 like
  • 4 in conversation