BookmarkSubscribeRSS Feed
Zoe8SAS
Fluorite | Level 6

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

5 REPLIES 5
ballardw
Super User

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?

Reeza
Super User
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.
Zoe8SAS
Fluorite | Level 6

Thanks!

 

Does this restore the originals?

proc template;
	delete base.freq.onewaylist;
run; 
Reeza
Super User
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?
Zoe8SAS
Fluorite | Level 6

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2833 views
  • 2 likes
  • 3 in conversation