I have tried going through all sorts of code and books to figure out how to make my output report look like the attached. Tried across/tabulate/transposing and then output and nothing looks even close.
Data Have:
Strata | location | procedure | value_display |
593415206 | ofc | caths | 2/ 2121 |
593415206 | ofc | pcis | ./ 2121 |
593415206 | ofc | imppacs | ./ 2121 |
593415206 | ofc | secho | 94/ 2121 |
593415206 | ofc | pet | ./ 2121 |
593415206 | ofc | tte | 448/ 2121 |
593415206 | ofc | tee | ./ 2121 |
593415206 | ofc | perfs | 262/ 2121 |
593415206 | ofc | cts | ./ 2121 |
593415206 | ofc | cabgs | ./ 2121 |
593415206 | ip | caths | 47/ 2121 |
593415206 | ip | pcis | 31/ 2121 |
593415206 | ip | imppacs | 5/ 2121 |
593415206 | ip | secho | 1/ 2121 |
593415206 | ip | pet | ./ 2121 |
593415206 | ip | tte | 285/ 2121 |
593415206 | ip | tee | 24/ 2121 |
593415206 | ip | perfs | 13/ 2121 |
593415206 | ip | cts | 4/ 2121 |
593415206 | ip | cabgs | ./ 2121 |
593415206 | op | caths | 29/ 2121 |
593415206 | op | pcis | 5/ 2121 |
593415206 | op | imppacs | 4/ 2121 |
593415206 | op | secho | 6/ 2121 |
593415206 | op | pet | ./ 2121 |
593415206 | op | tte | 160/ 2121 |
593415206 | op | tee | 7/ 2121 |
593415206 | op | perfs | 28/ 2121 |
593415206 | op | cts | 2/ 2121 |
593415206 | op | cabgs | ./ 2121 |
593415206 | ofc | revascs | ./ 78 |
593415206 | ip | revascs | 28/ 78 |
593415206 | op | revascs | 3/ 78 |
593415206 | ofc | outptpci | ./ 36 |
593415206 | ip | outptpci | 3/ 36 |
593415206 | op | outptpci | 3/ 36 |
593415206 | ofc | minrestudy | ./ 25 |
593415206 | ip | minrestudy | 3/ 25 |
593415206 | op | minrestudy | 1/ 25 |
593415206 | ofc | majrestudy | ./ 25 |
593415206 | ip | majrestudy | 4/ 25 |
593415206 | op | majrestudy | ./ 25 |
593415206 | ofc | pcifu30 | ./ 32 |
593415206 | ip | pcifu30 | 3/ 32 |
593415206 | op | pcifu30 | ./ 32 |
593415206 | ofc | pcifu182 | ./ 17 |
593415206 | ip | pcifu182 | 3/ 17 |
593415206 | op | pcifu182 | ./ 17 |
What it needs to look like when I do a proc report to an RTF:
FREQUENCY COUNTS BY PLACE OF SERVICE | |||
Cardiac Metric Component | INPATIENT | OUTPATIENT | OFFICE CLINIC |
1-182 DAY PCI REDO RATE | 3/17 | ./17 | ./17 |
1-30 DAY PCI REDO RATE | 3/32 | ./32 | ./32 |
3-MONTH MAJOR RESTUDY RATE | 4/25 | ./25 | ./25 |
3-MONTH MINOR RESTUDY RATE | 3/25 | 1/25 | ./25 |
CABG RATE | ./2121 | ./2121 | ./2121 |
CARDIAC CT RATE | 4/2121 | 2/2121 | ./2121 |
CATH RATE | 47/2121 | 29/2121 | 2/2121 |
IMPLANT RATE | 5/2121 | 4/2121 | ./2121 |
OUTPATIENT PCI RATE | 3/36 | 3/36 | ./36 |
PCI RATE | 31/2121 | 5/2121 | ./2121 |
CARDIAC PET SCAN | ./2121 | ./2121 | ./2121 |
REVASC RATE | 28/78 | 3/78 | ./78 |
SPECT/PERFUSION RATE | 13/2121 | 28/2121 | 262/2121 |
STRESS ECHO RATE | 1/2121 | 6/2121 | 94/2121 |
TEE ECHO RATE | 24/2121 | 7/2121 | ./2121 |
TTE ECHO RATE | 285/2121 | 160/2121 | 448/2121 |
Hi,
But, in looking over the original post again, I don't think that the OP needs TRANSPOSE at all. I think that PROC REPORT can do this. All you need is to make a TYPE variable that you can use for ACROSS usage on the report. For example, the code below produced the attached screenshot on the untransposed data, only using a TYPE variable. As you can see in the screen shot, you can either use STRATA as a BY variable or put it on the report. That is up to you.
cynthia
data have;
length strata 8 location $4 procedure $10 value_display $10;
infile datalines dlm=',' dsd;
input Strata location $ procedure $ value_display $;
** make TYPE variable for PROC REPORT;
if location = 'ip' then type='1';
else if location = 'op' then type = '2';
else if location = 'ofc' then type = '3';
datalines;
593415206,ofc, caths ,2/ 2121
593415206,ofc, pcis ,./ 2121
593415206,ofc, imppacs ,./ 2121
593415206,ofc, secho , 94/ 2121
593415206,ofc, pet ,./ 2121
593415206,ofc, tte ,448/ 2121
593415206,ofc, tee ,./ 2121
593415206,ofc, perfs, 262/ 2121
593415206,ofc, cts , ./ 2121
593415206,ofc, cabgs , ./ 2121
593415206,ip, caths ,47/ 2121
593415206,ip, pcis ,31/ 2121
593415206,ip, imppacs , 5/ 2121
593415206,ip, secho , 1/ 2121
593415206,ip, pet , ./ 2121
593415206,ip, tte , 285/ 2121
593415206,ip, tee ,24/ 2121
593415206,ip, perfs ,13/ 2121
593415206,ip, cts , 4/ 2121
593415206,ip, cabgs , ./ 2121
593415206,op, caths ,29/ 2121
593415206,op, pcis , 5/ 2121
593415206,op, imppacs, 4/ 2121
593415206,op, secho , 6/ 2121
593415206,op, pet , ./ 2121
593415206,op, tte , 160/ 2121
593415206,op, tee , 7/ 2121
593415206,op, perfs ,28/ 2121
593415206,op, cts , 2/ 2121
593415206,op, cabgs , ./ 2121
593415206,ofc, revascs, ./ 78
593415206,ip, revascs, 28/ 78
593415206,op, revascs , 3/ 78
593415206,ofc, outptpci, ./ 36
593415206,ip, outptpci, 3/ 36
593415206,op, outptpci, 3/ 36
593415206,ofc, minrestudy, ./ 25
593415206,ip, minrestudy, 3/ 25
593415206,op, minrestudy, 1/ 25
593415206,ofc, majrestudy, ./ 25
593415206,ip, majrestudy, 4/ 25
593415206,op, majrestudy, ./ 25
593415206,ofc, pcifu30 ,./ 32
593415206,ip, pcifu30 ,3/ 32
593415206,op, pcifu30 ,./ 32
593415206,ofc, pcifu182, ./ 17
593415206,ip, pcifu182, 3/ 17
593415206,op, pcifu182, ./ 17
;
run;
ods listing close;
proc format;
value $typ '1' = 'In Patient'
'2' = 'Out Patient'
'3' = 'Office Visit';
run;
proc sort data=have out=have;
by strata procedure type;
run;
ods listing close;
ods rtf file='c:\temp\strata.rtf';
title 'Report Without Transpose';
** the FAKEVAR variable is needed because value_display is a character var;
** and PROC REPORT needs to have some number to summarize, so just give it a 1;
** and the rows will collapse correctly for this report.;
proc report data=have nowd;
by strata;
column ('Strata' strata) ('Cardiac Metric Component' procedure) type,value_display fakevar;
define strata / group ' ' /* Noprint */;
define procedure / group ' ' style(column)={width=2in};
define type / across ' ' f=$typ. order=data;
define value_display / display ' ' style(column)={just=r};
define fakevar/ computed noprint;
compute fakevar;
fakevar=1;
endcomp;
run;
ods rtf close;
title;
Assuming there is only one strata for each report/dataset run . . . and assuming there is every only one crossing of LOCATION and PROCEDURE (for every unique strata). Note you will need to substitute your procedure code descriptions in the code below (rather than using the procedure codes themselves).
There are lots of different ways to do this in SAS . . . this is only one approach.
proc sort data=have;
by procedure ;
data want (drop=strata location value);
set have ;
format inpatient outpatient office $10.;
label office = 'OFFICE CLINIC';
by procedure ;
retain inpatient outpatient office ;
if first.procedure then do ;
inpatient='';
outpatient='';
office='';
end;
if location = 'ip' then inpatient = value;
if location = 'op' then outpatient = value;
if location = 'ofc' then office = value;
if last.procedure then output;
run;
proc format;
value $CMC
'pcifu182'='1-182 DAY PCI REDO RATE'
'pcifu30'='1-30 DAY PCI REDO RATE'
/* more formats to cover all distinct procedure */
;
run;
proc transpose data=have out=want;
by strata procedure;
id location;
var value_display;
run;
ods rtf file='c:\somefile.rtf';
proc print data=want;
by strata;
format procedure $CMC.;
run;
ods rtf close;
The syntax is only an indication of how to go I can't test this from home at all. If you have an existing dataset with the full procedure name / procedure acronym you could/should use that instead of the proc format just to avoid hard coding the proc format for all your different procedures.
If you want to change the name of the variables created by transpose (IP OP OFC) you can use datasets options label=(var1='label1' var2='label2') in either the proc print data=want() options or even prior to this if you ever plan to do other manipulation of the want dataset you can do it at output time on the proc transpose data=have out=want(rename=(IP=INPATIENT OP=OUTPATIENT OFC=OFFICECLINIC))
For your desired output I'd likely use the label statement in the proc print simply because it allows you to use sentences rather than var names respecting SAS varnames. The big chunk really is how to use proc transpose to get a dataset that respects your output objectives.
Proc report procedure wouldn't offer you much more than proc print unless you intended to play a lot with fonts/colors/size etc.
*edit corrected for format. Moved it as a statement in the procedure instead of a dataset option.
Vincent
Hi:
FORMAT= is NOT a SAS dataset option
proc print data=want(format=(procedure=$CMC.));
SAS(R) 9.4 Data Set Options: Reference
TABULATE has a FORMAT= option, but there is no FORMAT= dataset option, such as shown in the previous post.
cynthia
Hi,
But, in looking over the original post again, I don't think that the OP needs TRANSPOSE at all. I think that PROC REPORT can do this. All you need is to make a TYPE variable that you can use for ACROSS usage on the report. For example, the code below produced the attached screenshot on the untransposed data, only using a TYPE variable. As you can see in the screen shot, you can either use STRATA as a BY variable or put it on the report. That is up to you.
cynthia
data have;
length strata 8 location $4 procedure $10 value_display $10;
infile datalines dlm=',' dsd;
input Strata location $ procedure $ value_display $;
** make TYPE variable for PROC REPORT;
if location = 'ip' then type='1';
else if location = 'op' then type = '2';
else if location = 'ofc' then type = '3';
datalines;
593415206,ofc, caths ,2/ 2121
593415206,ofc, pcis ,./ 2121
593415206,ofc, imppacs ,./ 2121
593415206,ofc, secho , 94/ 2121
593415206,ofc, pet ,./ 2121
593415206,ofc, tte ,448/ 2121
593415206,ofc, tee ,./ 2121
593415206,ofc, perfs, 262/ 2121
593415206,ofc, cts , ./ 2121
593415206,ofc, cabgs , ./ 2121
593415206,ip, caths ,47/ 2121
593415206,ip, pcis ,31/ 2121
593415206,ip, imppacs , 5/ 2121
593415206,ip, secho , 1/ 2121
593415206,ip, pet , ./ 2121
593415206,ip, tte , 285/ 2121
593415206,ip, tee ,24/ 2121
593415206,ip, perfs ,13/ 2121
593415206,ip, cts , 4/ 2121
593415206,ip, cabgs , ./ 2121
593415206,op, caths ,29/ 2121
593415206,op, pcis , 5/ 2121
593415206,op, imppacs, 4/ 2121
593415206,op, secho , 6/ 2121
593415206,op, pet , ./ 2121
593415206,op, tte , 160/ 2121
593415206,op, tee , 7/ 2121
593415206,op, perfs ,28/ 2121
593415206,op, cts , 2/ 2121
593415206,op, cabgs , ./ 2121
593415206,ofc, revascs, ./ 78
593415206,ip, revascs, 28/ 78
593415206,op, revascs , 3/ 78
593415206,ofc, outptpci, ./ 36
593415206,ip, outptpci, 3/ 36
593415206,op, outptpci, 3/ 36
593415206,ofc, minrestudy, ./ 25
593415206,ip, minrestudy, 3/ 25
593415206,op, minrestudy, 1/ 25
593415206,ofc, majrestudy, ./ 25
593415206,ip, majrestudy, 4/ 25
593415206,op, majrestudy, ./ 25
593415206,ofc, pcifu30 ,./ 32
593415206,ip, pcifu30 ,3/ 32
593415206,op, pcifu30 ,./ 32
593415206,ofc, pcifu182, ./ 17
593415206,ip, pcifu182, 3/ 17
593415206,op, pcifu182, ./ 17
;
run;
ods listing close;
proc format;
value $typ '1' = 'In Patient'
'2' = 'Out Patient'
'3' = 'Office Visit';
run;
proc sort data=have out=have;
by strata procedure type;
run;
ods listing close;
ods rtf file='c:\temp\strata.rtf';
title 'Report Without Transpose';
** the FAKEVAR variable is needed because value_display is a character var;
** and PROC REPORT needs to have some number to summarize, so just give it a 1;
** and the rows will collapse correctly for this report.;
proc report data=have nowd;
by strata;
column ('Strata' strata) ('Cardiac Metric Component' procedure) type,value_display fakevar;
define strata / group ' ' /* Noprint */;
define procedure / group ' ' style(column)={width=2in};
define type / across ' ' f=$typ. order=data;
define value_display / display ' ' style(column)={just=r};
define fakevar/ computed noprint;
compute fakevar;
fakevar=1;
endcomp;
run;
ods rtf close;
title;
This was awesome. This helped me out so much. Thank you. I now should be able to figure out my other output using this as an example. I will have to figure out how to do the right calculation but that is something entirely different. Thanks again.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: