I am putting some summary tables in datasets to print for a report. Below, I copied the code to save the label to come with the datasets:
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;
I wanted to keep the labels; however, I do not want the labels to be on every row of my tables.
Is there any way I can print the label only once? Please see an example of my output report below:
AGE |
label |
Frequency |
Percent |
L_95_p_CI |
U_95_p_CI |
10-20 years |
Age categories |
379 |
19.5 |
17.1 |
21.9 |
21-30 years |
Age categories |
460 |
23.7 |
21.3 |
26.1 |
31-40 years |
Age categories |
380 |
19.6 |
17.6 |
21.5 |
41-50 years |
Age categories |
369 |
19.0 |
17.0 |
20.9 |
51-60 years |
Age categories |
354 |
18.3 |
16.6 |
19.9 |
Thanks
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
PROC PRINT and PROC REPORT allow you to choose which data set variables get printed;
proc print data=yourdatasetname;
var age frequency percent l_95_p_ci u_95_p_ci;
run;
Proc Report would show the default assigned variable label as a column heading.
This adds a new label to the variable name in a data set you should have available to demonstrate.
data work.class; set sashelp.class; label name='Student name'; run; proc report data=work.class; columns name sex age; run;
Or you can provide a label to override the existing one in a define statement:
proc report data=work.class; columns name sex age ; define age/ 'Age at enrollment'; run;
Or use a variable as a Group variable
proc report data=work.class; columns sex name age ; define sex / group; define age/ 'Age at enrollment'; run;
Though Proc Report would want the "group" variable text to appear to the left of the categories. Note that the Columns statement provides the order left to right of the columns that appear in the output.
SAS makes it hard for us to do things that are not logical.
You've shown us so far only your not desired report. How should your desired report look like?
@Emma_at_SAS wrote:
Thank you, ballardw, for your suggestion. I want to keep the variable name age as is. I want to add the label in some form once. the label is not really a label anymore. It is a column of my dataset that is repeating itself for all rows, which makes it too repetitive in a long report. Thanks
That is exactly what a GROUP variable does in proc report.
Provide and actual example of your data set in the form of data step code and I'll show you how. Though the last example I provided should work: change the data set to yours, your variables on the columns state with the Label variable first, Define the Label variable as a Group variable.
Good point, Patrik. My ideal table looks like this:
Age categories
AGE |
Frequency |
Percent |
L_95_p_CI |
U_95_p_CI |
10-20 years |
379 |
19.5 |
17.1 |
21.9 |
21-30 years |
460 |
23.7 |
21.3 |
26.1 |
31-40 years |
380 |
19.6 |
17.6 |
21.5 |
41-50 years |
369 |
19.0 |
17.0 |
20.9 |
51-60 years |
354 |
18.3 |
16.6 |
19.9 |
Thanks
Seems simple enough.
Since you didn't provide any example data let's convert your posted table into a dataset.
data have ;
infile cards truncover ;
input
(AGE
label
Frequency
Percent
L_95_p_CI
U_95_p_CI ) ($50./);
cards;
10-20 years
Age categories
379
19.5
17.1
21.9
21-30 years
Age categories
460
23.7
21.3
26.1
31-40 years
Age categories
380
19.6
17.6
21.5
41-50 years
Age categories
369
19.0
17.0
20.9
51-60 years
Age categories
354
18.3
16.6
19.9
;
So just use BY statement. For example with PROC REPORT.
proc report data=have ;
by label ;
column age
Frequency
Percent
L_95_p_CI
U_95_p_CI
;
define _all_ / display;
run;
Results
Thanks, ballardw. I try your previous code and will send an update shortly. Thanks
Thanks, ballardw. I tried the GROUP code but I think I am doing something wrong because it grouped all rows. My code and the new table are copied below. I am going to send my full code in a separate post. Thanks
proc report data=output3;
columns label &varname Frequency Percent L_95_p_CI U_95_p_CI ;
define label / group;
run;
label | Age categories | Weighted Frequency | Percent | 95% Lower Confidence Limit, Percent | 95% Upper Confidence Limit, Percent |
---|---|---|---|---|---|
Age categories | 15 | 1941 | 100.0 | 89.7 | 110.3 |
Below is the full code that I am using. I hope it helps. Thanks
%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;
data output3;
retain &varname
label
Frequency
Percent
L_95_p_CI
U_95_p_CI ;
set output2;
run;
ods exclude none;
proc print data=output3 noobs; run; *this is the table that goes into report;
%mend;
ods word file="D:\path\PROC_SURVEYFREQ.docx" STARTPAGE=NO;
%macro_surveyfreq(age);
%macro_surveyfreq(gender_3_levels);
%macro_surveyfreq(education_level);
%macro_surveyfreq(income_4_cat);
*my list is long here;
ods word close;
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.