BookmarkSubscribeRSS Feed
gary101
Calcite | Level 5

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

 

3 REPLIES 3
gary101
Calcite | Level 5

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

 

 

Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 743 views
  • 0 likes
  • 3 in conversation