topic remove title from export multiple data sets into multiple sheets in excel in ODS and Base Reporting

remove title from export multiple data sets into multiple sheets in excel

magicdj — Wed, 22 May 2024 16:02:33 GMT

i found an old post in link below :

my code:

data set1;

set sashelp.baseball ;

run;

proc sort; by team ; run;
ods excel file="C:\Users\test.xlsx" options(sheet_interval='BYGROUP' sheet_interval='BYGROUP' sheet_name= '#byval1' );

proc print data=set1;
by team;
run;
ods excel close;

Solved: export multiple data sets into multiple sheets in excel - SAS Support Communities

the problem is that row 1 has title and row 2 is empty. how to remove row 1 and 2 to make it looks like excel , not report. also for column Obs, is there any possible to delete or use other variable to instead? thanks.

Re: remove title from export multiple data sets into multiple sheets in excel

Reeza — Wed, 22 May 2024 16:12:58 GMT

Add NOOBS to proc print statement to remove the OBS.

Changing the spacing is a bit harder unfortunately 😞

proc print data=set1 NOOBS;

Re: remove title from export multiple data sets into multiple sheets in excel

magicdj — Wed, 22 May 2024 16:19:25 GMT

so there is no way to remove title? that's sad. noobs works

Re: remove title from export multiple data sets into multiple sheets in excel

PaigeMiller — Wed, 22 May 2024 16:23:18 GMT

In SAS, before PROC PRINT, this command works:

title;

ods excel file="C:\Users\test.xlsx" options(sheet_interval='BYGROUP' sheet_interval='BYGROUP' sheet_name= '#byval1' 
    embedded_titles='NO');

Re: remove title from export multiple data sets into multiple sheets in excel

magicdj — Wed, 22 May 2024 16:32:31 GMT

thanks for reply. tried both. none of them working, not sure why

Re: remove title from export multiple data sets into multiple sheets in excel

PaigeMiller — Wed, 22 May 2024 16:36:08 GMT

Here in the SAS forums, saying you tried it and it did not work is never sufficient. This gives us no information about what went wrong. You have to show us the code you are using, and the result that isn't what you want. If there is an error in the log, we also need to see the part of the log containing the code of interest (and not just the error messages but the entire code of interest).

Re: remove title from export multiple data sets into multiple sheets in excel

Reeza — Wed, 22 May 2024 16:52:22 GMT

Try the NOBYLINE option.

For some reason I interpreted your question as removing the space (not enough coffee).

options nobyline;
title;

@magicdj wrote:

so there is no way to remove title? that's sad. noobs works

data set1;

set sashelp.baseball ;

run;

proc sort data=set1; by team ; run;


ods excel file="/home/fkhurshed/Demo1/demo_delete.xlsx" options(sheet_interval='BYGROUP' sheet_interval='BYGROUP' sheet_name= '#byval1' );

options nobyline;
title;
proc print data=set1 noobs;
by team;
run;
ods excel close;

Re: remove title from export multiple data sets into multiple sheets in excel

magicdj — Wed, 22 May 2024 17:28:24 GMT

thanks Reeza and Paige.