topic Re: SAS ODS EXCEL OUTPUT in ODS and Base Reporting

SAS ODS EXCEL OUTPUT

SASUSER21 — Wed, 10 May 2017 13:46:55 GMT

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?

Re: SAS ODS EXCEL OUTPUT

PaigeMiller — Wed, 10 May 2017 13:51:09 GMT

I think you want the SHEET_INTERVAL='NONE' option

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

Re: SAS ODS EXCEL OUTPUT

SASUSER21 — Wed, 10 May 2017 14:27:33 GMT

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

Re: SAS ODS EXCEL OUTPUT

RW9 — Wed, 10 May 2017 14:56:18 GMT

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#p09n5pw9ol0897n1qe04zeur27rv.htm

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

Re: SAS ODS EXCEL OUTPUT

SASUSER21 — Wed, 10 May 2017 15:50:14 GMT

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.

Re: SAS ODS EXCEL OUTPUT

RW9 — Wed, 10 May 2017 16:27:34 GMT

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.

Re: SAS ODS EXCEL OUTPUT

Chevell_sas — Thu, 11 May 2017 15:23:31 GMT

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;

Re: SAS ODS EXCEL OUTPUT

rkval — Thu, 28 Jun 2018 22:58:51 GMT

I believe you can now use proc export along with ODS excel to "append" new data to the existing files without repeating the headers. HTH

Re: SAS ODS EXCEL OUTPUT

Vince_SAS — Thu, 28 Jun 2018 23:26:10 GMT

If you license the SAS/ACCESS Interface to PC Files, and you want only the data and no colors or other markup, then this code gives you what you want:

*  206,160 rows and 22 columns;

data work.big_zipcode;
set sashelp.zipcode;
do i = 1 to 5;
  output;
end;
run;

*  All 206,160 rows and 22 columns written without an out-of-memory error;

proc export data=work.big_zipcode
  file='C:\temp\big_zipcode.xlsx'
  dbms=xlsx
  replace;
run; quit;

Vince DelGobbo

SAS R&D