Modifying how to print a dataset

[Reeza]

%macro macro_surveyfreq(varname);
ods exclude all;
ods output OneWay=Output; 
proc surveyfreq data = &dataset VARHEADER = NAMELABEL nosummary;
    tables &varname/cl nofreq nostd; 
	weight &weight_var;
run;
data output;
    set output;
    label = vlabelx(scan(table,2,' '));
run;
proc print data=output; run;

data output1; 
set output (drop=Table F_&varname _SkipLine) end=last; 
if not last then output;
run;

data output2 (rename= (WgtFreq=Frequency LowerCL=L_95_p_CI UpperCL=U_95_p_CI));
set output1;
run;
proc print data=output2 noobs; run;

proc sql noprint;
select distinct label into :title_name from output2;
quit;

data output3;
   retain &varname 
	Frequency
	Percent 
	L_95_p_CI 
	U_95_p_CI ;
   set output2;
drop label;
run;

ods exclude none;

title j=l "&title_name.";
proc print data=output3 noobs; run; *this is the table that goes into report;

%mend;

[Emma_at_SAS · Accepted solution]

Thank you, everyone!

For more information please review the last post from Tom and Reeza.

In summary, an efficient way to remove repeated labels and add the label at the top of the table is to save the label, for example in title_name

proc sql noprint;
select distinct label into :title_name from output2;
quit;

 and then call that title_name in a title when printing the output

title j=l "&title_name.";

Thanks

[Emma_at_SAS]

Thank you, Reeza. I used this in my code and it works perfectly.

[Emma_at_SAS]

Reeza, thank you again for your code. Now, I want to use it on another occasion but I could not modify it very well.

I am using the following code to save the labels and print them above the tables in my report. In my SAS output, it works perfect and copies each label above each table (sample output below)

AGE: How old are you?

VarName	Mean	SD	L_95_p_CI	U_95_p_CI	N
AGE	39.88	14.25	39.14	40.62	1250

However, in my ODS WORD file, only the label for the first variable in the list appears. May you please help me to resolve this issue, please? Thanks

%macro do_sm(varname);
ods exclude all;
data output3;
   retain VarName 
	VarLabel
	Mean
	&varname
	LowerCLMean
	UpperCLMean
	N;
   set output2;
run;

data output4 (rename=(VarName=VarName
						   VarLabel=Label
						   &varname=SD
						   LowerCLMean=L_95_p_CI 
						   UpperCLMean=U_95_p_CI));
set output3;
run;

proc sql noprint;
select distinct Label into :title_name from output4;
quit;

data output5 (drop=Label); set output4; run;

ods exclude none;

title j=l "&title_name.";
proc print data=output5 noobs; run;

             
%mend do_sm;

ods word file="D:path\REPORT_&DATE..docx" STARTPAGE=NO;
OPTIONS NONUMBER NODATE FORMDLIM='~';
%do_sm(age);
%do_sm(money);
ods word close;

[Reeza]

Reset title name between runs so set it to missing before the PROC SQL. I'm guessing that money doesn't actually have a label so it stays with the old value.

*set to missing;
%let title_name = ;
proc sql noprint;
select distinct Label into :title_name from output4;
quit;

[Emma_at_SAS]

Thank you very much, Reeza. The logic of your solution makes sense but that did not solve the issue in my WORD output. May this be an issue with ODS WORD, because the SAS output distinguishes all LABELs correctly?

I tried ODS RTF and I could get labels for the first variable on each separate page.

Thanks

[Reeza]

WORD is still pre-production so that's to be expected unfortunately.

[Emma_at_SAS]

Thank you, Reeza, for your thoughts on this, very much appreciated!

[Emma_at_SAS]

As a quick update, I tried ODS EXCEL and that works. I will create an Excel file and copy the results into a Word file. Thanks