topic Remove row under title of ODS EXCEL and PROC FREQ output in New SAS User

Remove row under title of ODS EXCEL and PROC FREQ output

Zoe8SAS — Wed, 14 Oct 2020 16:52:38 GMT

I'm using ODS EXCEL to export results produced by PROC FREQ to Excel. The output includes an empty row between the table title and the start of the table (see highlighted in yellow in Excel file). How can I delete that row in my SAS code?

Do I do this in the PROC TEMPLATE or in the ODS EXCEL statement?

Here's my sample code:

*Create sample dataset;
data test;
	set 	sashelp.Class;
	run;

*Create template;
	proc template;
		 edit  base.freq.onewaylist;
			 edit FVariable;                                                       
			 	just=varjust;
				style=rowheader;
				header="Age at survey date";
			end;
		 end;
	run;

ods excel file="PATHNAME\test.xlsx" 
	options(sheet_name="Female" 
			embedded_titles="yes"
			embed_titles_once="yes");

	proc freq data = test(where=(sex="F"));
		tables age;
		title "Age at survey for female participants";
	run;

ods excel close;

proc template;
	delete base.freq.onewaylist;
run;

Thank you!

Re: Remove row under title of ODS EXCEL and PROC FREQ output

Reeza — Wed, 14 Oct 2020 17:02:31 GMT

Typically done via PROC TEMPLATE but could also be accomplished using HIDDEN_ROWS option and possibly the rowbreaks_interval depending on how dynamic you need it to be.

Re: Remove row under title of ODS EXCEL and PROC FREQ output

Zoe8SAS — Wed, 14 Oct 2020 17:19:32 GMT

Thanks! I want to delete it, not just hide it.

How would I do this in PROC TEMPLATE?

Re: Remove row under title of ODS EXCEL and PROC FREQ output

Reeza — Wed, 14 Oct 2020 17:26:42 GMT

Not easily? First you'd have to figure out which parameter/setting controlled that space (top/bottom margin) in the ODS EXCEl, figure out which style you're using and the parent and modify it accordingly.

Here's a rough idea of what that looks like the proc template code at least.
https://support.sas.com/kb/39/540.html

Re: Remove row under title of ODS EXCEL and PROC FREQ output

Zoe8SAS — Thu, 15 Oct 2020 14:34:20 GMT

Thanks! I will delete it in Excel for now and then look more closely at the proc template code.