proc report to rtf problem

[tmcrouse]

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

[Fugue]

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;

[Vince28_Statcan]

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

[Cynthia_sas]

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

[Cynthia_sas · Accepted solution]

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;

[tmcrouse]

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.