BookmarkSubscribeRSS Feed
RoyPardee
Obsidian | Level 7

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

 

 

2 REPLIES 2
Shmuel
Garnet | Level 18

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;

 

 

RoyPardee
Obsidian | Level 7

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|
+-----------------+-----------------+------------+------------+------------+  

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 1024 views
  • 0 likes
  • 2 in conversation