topic simple report question in SAS Programming

simple report question

gary101 — Tue, 06 Dec 2016 14:33:14 GMT

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

A simple report question. Please help.

gary101 — Tue, 06 Dec 2016 14:30:26 GMT

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

Re: simple report question

Astounding — Tue, 06 Dec 2016 14:54:08 GMT

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.

Re: simple report question

RW9 — Tue, 06 Dec 2016 15:01:07 GMT

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.