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
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;
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| +-----------------+-----------------+------------+------------+------------+
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.