%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;
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.