topic Re: Use variable label as column header for PROC FREQ while suppressing H1 title in New SAS User

Use variable label as column header for PROC FREQ while suppressing H1 title

Zoe8SAS — Thu, 15 Oct 2020 14:38:33 GMT

I'm using PROC TEMPLATE and PROC FREQ to produce a customized frequency table for a variable that has a variable label. I want to use the variable label as the column header of the first column in the resulting table, but I don't want it to appear as the subtitle (h1 title). Is there a way to do this without hard-coding the column header in PROC TEMPLATE?

I first tried the following:

data test;
	set 	sashelp.Class;
	label age='Age at survey';
run;

proc template;
		 edit  base.freq.onewaylist;
			 edit FVariable;                                                       
			 	just=varjust;
				style=rowheader;
				header=varlabel;
			end;
		 end;
run;

ods excel file="PATHNAME\test1.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;

This puts the variable label "Age at survey" correctly as a header of column 1, but it also puts it as a subtitle. See test1 attached. I don't want the subtitle.

I know I can leave the subtitle blank using

proc template;
	edit  base.freq.onewaylist;
		edit h1;
			text " ";
		end;
		 edit FVariable;                                                       
		 	just=varjust;
			style=rowheader;
			header=varlabel;
		end;
	 end;
run;

But this still leaves a blank row - see test2 attached. I want to delete that row.

I get the desired output by hardcoding the column header in PROC TEMPLATE and suppressing the variable label in PROC FREQ using label age=" " :

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

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

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

ods excel close;

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

See test3 for desired output. But this template isn't flexible to be used for other variables.
Is there a way to produce the same output while using header=varlabel; when editing FVariable in PROC FREQ? Or is there any other better way to do this?

EDIT: replaced Excel examples with pdfs

Re: Use variable label as column header for PROC FREQ while suppressing H1 title

ballardw — Wed, 14 Oct 2020 18:05:07 GMT

I'm not going to open XLSX files from an unknown source.

Instead of fighting with templates have you considered another reporting procedure such as Proc Report or Tabulate you can apply styles and such per variable in table descriptions?

Re: Use variable label as column header for PROC FREQ while suppressing H1 title

Reeza — Wed, 14 Oct 2020 19:07:35 GMT

Seconding PROC TABULATE which will give you more control over your output directly. Messing around with templates is complicated and gets dangerous - make sure to save a version to restore back your originals in case.

Re: Use variable label as column header for PROC FREQ while suppressing H1 title

Zoe8SAS — Thu, 15 Oct 2020 14:39:33 GMT

Good point! I replaced the Excel files with pdfs. I'll look into report and tabulate. Thanks!

Re: Use variable label as column header for PROC FREQ while suppressing H1 title

Zoe8SAS — Thu, 15 Oct 2020 14:40:35 GMT

Thanks!

Does this restore the originals?

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

Re: Use variable label as column header for PROC FREQ while suppressing H1 title

Reeza — Thu, 15 Oct 2020 15:27:41 GMT

No, that deletes the templates. It's possible SAS has changed how they store it since the last time I messed around with templates but if you run PROC FREQ after running that what happens?