Hi all,
I have the data of Age, Sex, Height, assume age is character variable.
In proc report taken group variabe as age, this age i need to display their gender and sum of height.
Actual report and final output is attached in image format.
what i need is if group variable contains single row then instead of showing that row, group variable is sufficient.
I followed below code for this, but there is lot of steps in my code.
Is there any method to reduce the code in proc report it self.
proc sql; create table class_mod as select distinct *, count(age) as count from sashelp.class group by age; quit; data class; retain age1 sex height; set class_mod; drop age; age1=input(age,$20.); rename age1=age; keep age1 sex height count; where age in (14,15,16); run; proc report data=class nowd list out=report_class; column age sex height count; define age/ group; DEFINE Height / SUM; break after age/summarize ; compute after age; age=catx('-','Avg-value for',age); endcomp; rbreak after/summarize; compute after; age='Grand total'; endcomp; run; data report_class; set report_class; if count=1 and _BREAK_=" " then delete; run; proc report data=report_class nowd list out=report_class1; column age sex height; define age/ group order=data; run;
I don't know if this can be done within the proc itself. I don't think so. If nobody else drops some knowledge on this, I'd recommend doing the pre-summarization yourself and deleting the extraneous column to get the desired effect.
Also, your output looks like it's summing the age, not averaging. I'm assuming that's an error, correct?
I'm also not clear on why you have the repeatd M M and F F within an age. Sorry for all the questions!
Formatting, tweaks aside, my assumption is that you're looking for something like this:
WPS/SAS: Using SQL union to create sections of a report
You do not need WPS for this( Ijust like to check WPS)
Using SQL union to create sections of a report
You may not like this solution but I find it much more flexible to
preprocess to get the data yhe way you want it and then use
just a dumb proc report ot proc print.
inspired by
https://goo.gl/cUK8u1
https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-Report-avoid-single-group-wise-frequency/m-p/339709
HAVE (subset of sashelp.class of 14-16 year olds)
===================================================
Up to 40 obs WORK.CLASS total obs=9
Obs SEX AGE HEIGHT
1 M 14 69.0
2 F 14 62.8
3 M 14 63.5
4 F 15 62.5
5 F 14 64.3
6 F 15 66.5
7 M 16 72.0
8 M 15 67.0
9 M 15 66.5
WANT
Up to 40 obs WORK.WANT total obs=12
===================================
Obs MJR SEX HEIGHT
1 14 F 62.800
2 F 64.300
3 M 63.500
4 M 69.000
5 Avg-value=14 64.900
6 15 F 62.500
7 F 66.500
8 M 66.500
9 M 67.000
10 Avg-value=15 65.625
11 Avg-value=16 72.000
12 Grand total 594.100
WORKING CODE
============
select case when count(*)=1 then 'ZZZ' else 'IND' end as grp, age, sex, height from class group by age, sex
union
select 'MEA' as grp, age, 'Q' as sex, mean(height) as height from class group by age
union
select 'SUM' as grp, 99 as age, 'Z' as sex, sum(height) as height from class
SOLUTION
========
proc datasets lib=work kill;
run;quit;
* create some data;
options validvarname=upcase;
data class;
set sashelp.class(where=(age>13) drop=name weight);
run;quit;
%utl_submit_wps64('
libname wrk "%sysfunc(pathname(work))";
proc sql;
create table wrk.rptpre(where=( grp ne "ZZZ")) as
select case when count(*)=1 then "ZZZ" else "IND" end as grp, age, sex, height from wrk.class group by age, sex
union
select "MEA" as grp, age, "Q" as sex, mean(height) as height from wrk.class group by age
union
select "SUM" as grp, 99 as age, "Z" as sex, sum(height) as height from wrk.class
order by age
;quit;
');
/*
Up to 40 obs WORK.RPTPRE total obs=12
Obs GRP AGE SEX HEIGHT
1 IND 14 F 62.800
2 IND 14 F 64.300
3 IND 14 M 63.500
4 IND 14 M 69.000
5 MEA 14 Q 64.900
6 IND 15 F 62.500
7 IND 15 F 66.500
8 IND 15 M 66.500
9 IND 15 M 67.000
10 MEA 15 Q 65.625
11 MEA 16 Q 72.000
12 SUM 99 Z 594.100
*/
%utl_submit_wps64('
libname wrk "%sysfunc(pathname(work))";
data wrk.want;
length mjr $32;
retain mjr " ";
set wrk.rptpre;
by age;
select;
when ( first.age and grp="IND") mjr=put(age,2.);
when ( last.age and grp="MEA") do; mjr=catx("=","Avg-value",put(age,2.)); sex="";end;
when ( grp="SUM") do; mjr="Grand total";sex=" ";end;
otherwise do;mjr=" ";end;
end;
keep mjr sex height;
run;quit;
');
/*
Up to 40 obs WORK.WANT total obs=12
Obs MJR SEX HEIGHT
1 14 F 62.800
2 F 64.300
3 M 63.500
4 M 69.000
5 Avg-value=14 64.900
6 15 F 62.500
7 F 66.500
8 M 66.500
9 M 67.000
10 Avg-value=15 65.625
11 Avg-value=16 72.000
12 Grand total 594.100
*/
A workaround way is using LINE + $varying.
But that might not be look like what you want.
data have;
set sashelp.class;
keep age sex height;
where age in (14,15,16);
run;
proc sort data=have;by age;run;
data want;
length char_age $ 40;
set have end=last;
by age;
array all{99999} _temporary_;
array h{9999} _temporary_;
if first.age then do;
call missing(of h{*});i=0;
char_age=put(age,best.);
end;
i+1;
h{i}=height;
n+1;
all{n}=height;
if not last.age then output;
else do;
if i=1 then do;char_age=cat('avg value for',age);sex=' ';output;end;
else do;
output;
char_age=cat('avg value for',age);
sex=' ';
height=sum(of h{*});
output;
end;
end;
if last then do;
char_age='Grand total:';
sex=' ';
height=sum(of all{*});
output;
end;
drop i n age;
run;
proc print noobs;run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.