topic Re: PROC REPORT noob--need to kind of transpose my data in SAS Programming

PROC REPORT noob--need to kind of transpose my data

RoyPardee — Mon, 24 Oct 2016 18:08:45 GMT

Hey All,

I've got patient-level data grouped by site, like so:

data have ;
  input
    @1    site  $char5.
    @7    mrn   $char6.
    @14   row1  2.0
    @18   row2  2.0
  ;
  label
    row1 = "No. flu shots"
    row2 = "No. broken arms"
  ;
datalines ;
east  roy     2  12
east  bob    21   .
east  mary    .   2
north sara    0   1
north trevor  7   .
south alice   1   0
south bart    1   0
south tim     0  31
south iggy    2   4
south gene    1   0
south don     0   0
run ;

So--data in columns (vars) that I would like to report as rows.

I can get pretty much a perfect transpose of the report I want with this:

proc report data = have ;
  column site row1 row2 ;
  define site / group ;
  define row1 / 'flu shots' ;
  define row2 / 'arms' ;
run ;

That spits out:

  site   flu shots       arms
  east          23         14
  north          7          1
  south          5         35

But what I really want is more like:

                    site
            east     north    south
flu shots     23         7        5
arms          14         1       35

My impulse was to change site from a /group var to an /across var. But when I do that, I get just a single row, with row counts under each listed site, followed by individual columns for my row1 & row2 vars, with sums over all sites.

I suspect that REPORT will easily do this for me, I just don't know how to make my wishes intelligible to it.

Can one of you kind gurus enlighten me?

Many thanks!

-Roy

Re: PROC REPORT noob--need to kind of transpose my data

Shmuel — Mon, 24 Oct 2016 19:30:35 GMT

By a middle datastep to prepare the data HAVE and using proc tabulate I got the wanted output:

data have ;
input
@1 site $char5.
@7 mrn $char6.
@14 row1 2.0
@18 row2 2.0@18 row2 2.0
;
label
row1 = "No. flu shots"
row2 = "No. broken arms"
;
datalines ;
east roy 2 12
east bob 21 .
east mary . 2
north sara 0 1
north trevor 7 .
south alice 1 0
south bart 1 0
south tim 0 31
south iggy 2 4
south gene 1 0
south don 0 0
run ;

data help;
set have;
length text $15;
keep site text row;
text ='flu shots';
row = row1; output;
text = 'arms';
row = row2; output;
run;

proc tabulate data=help;
class site text;
var row;
table text=' ',
site*row*sum=' '*f=5.;
run;

Re: PROC REPORT noob--need to kind of transpose my data

RoyPardee — Mon, 24 Oct 2016 23:05:51 GMT

Thanks Shmuel!

That solution is not super-appealing, unfortunately. In my real data I've got 87 rows I need to put out. I could actually run my dset through PROC TRANSPOSE I suppose, but I think I may just switch to PROC TABULATE for this. This code does what I want:

proc tabulate data = have ;
  class site ;
  var row1-row2 ;
  tables (row1-row2)*sum="", site / misstext = '0';
run ;

Output from that is:

+-----------------------------------+--------------------------------------+
|                                   |                 site                 |
|                                   +------------+------------+------------+
|                                   |    east    |   north    |   south    |
+-----------------+-----------------+------------+------------+------------+
|No. flu shots    |                 |       23.00|        7.00|        5.00|
+-----------------+-----------------+------------+------------+------------+
|No. broken arms  |                 |       14.00|        1.00|       35.00|
+-----------------+-----------------+------------+------------+------------+