Hi,
My question is pretty simple but I just can't figure it out. My data looks like
name trials benchpress legpress
gary 2 170 100
gary 3 150 90
gary 4 160 89
peter 1 180 90
peter 7 120 80
tom 3 100 50
tom 4 110 60
tom 5 80 40
...
Basically I just want to output a report that looks like this
name type mean
gary benchpress 160
gary legpress 95
peter benchpress 150
peter legpress 85
tom benchpress 105
tom legpress 50
Is there a simple one step procedure (proc report or proc summary) that can do this? or do I need some data step manipulation like proc transpose before I invoke procedure?
Thank you.
gary
Hi,
My question is pretty simple but I just can't figure it out. My data looks like
name trials benchpress legpress
gary 2 170 100
gary 3 150 90
gary 4 160 89
peter 1 180 90
peter 7 120 80
tom 3 100 50
tom 4 110 60
tom 5 80 40
...
Basically I just want to output a report that looks like this
name type mean
gary benchpress 160
gary legpress 95
peter benchpress 150
peter legpress 85
tom benchpress 105
tom legpress 50
Is there a simple one step procedure (proc report or proc summary) that can do this? or do I need some data step manipulation like proc transpose before I invoke procedure?
Thank you.
gary
PROC TABULATE would be a good tool for the job. Here's a simple method:
proc tabulate data=have;
class name;
var benchpress legpress;
tables name * (benchpress legpress), mean;
run;
The more you learn about PROC TABULATE, the more you can adjust the appearance of the report.
Whilst there may be some technical part of proc report that can do this for you, I always do my data processing before the reporting stage for a number of reasons. This will for instance get results for your test data you posted (good to post it as a datastep for future reference):
data have; input name $ trials benchpress legpress; datalines; gary 2 170 100 gary 3 150 90 gary 4 160 89 peter 1 180 90 peter 7 120 80 tom 3 100 50 tom 4 110 60 tom 5 80 40 ; run; proc sql; create table WANT as select NAME, "benchpress" as TYPE, mean(BENCHPRESS) as MEAN from HAVE group by NAME union all select NAME, "legpress" as TYPE, mean(LEGPRESS) as MEAN from HAVE group by NAME order by NAME,TYPE; quit;
However I suspect you have far more categories than that, so I would suggest going for a normalised data structure as this will make your coding so much easier.
data have (keep=name type val); input name $ trials benchpress legpress; do type="benchpress","legpress"; val=ifn(type="benchpress",benchpress,legpress); output; end; datalines; gary 2 170 100 gary 3 150 90 gary 4 160 89 peter 1 180 90 peter 7 120 80 tom 3 100 50 tom 4 110 60 tom 5 80 40 ; run; proc sort data=have; by name type; run; proc means data=have noprint; by name type; var val; output out=want mean=mean; run;
I have changed the test data to show a normalised structure, what it does is enable by group processing, which saves your program.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.