DATA Step, Macro, Functions and more

simple report question

Reply
New Contributor
Posts: 2

simple report question

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

 

New Contributor
Posts: 2

A simple report question. Please help.

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

 

 

Super User
Posts: 5,516

Re: simple report question

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.

Super User
Super User
Posts: 7,977

Re: simple report question

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.

Ask a Question
Discussion stats
  • 3 replies
  • 168 views
  • 0 likes
  • 3 in conversation