BookmarkSubscribeRSS Feed
SASUSER21
Fluorite | Level 6

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?

8 REPLIES 8
SASUSER21
Fluorite | Level 6

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

blank row.PNG

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SASUSER21
Fluorite | Level 6

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Chevell_sas
SAS Employee

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;

rkval
Fluorite | Level 6

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

Vince_SAS
Rhodochrosite | Level 12

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

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
  • 2221 views
  • 3 likes
  • 6 in conversation