Help using Base SAS procedures

proc report to rtf problem

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

proc report to rtf problem

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:

Stratalocationprocedurevalue_display
593415206ofccaths    2/ 2121
593415206ofcpcis    ./ 2121
593415206ofcimppacs    ./ 2121
593415206ofcsecho   94/ 2121
593415206ofcpet    ./ 2121
593415206ofctte  448/ 2121
593415206ofctee    ./ 2121
593415206ofcperfs  262/ 2121
593415206ofccts    ./ 2121
593415206ofccabgs    ./ 2121
593415206ipcaths   47/ 2121
593415206ippcis   31/ 2121
593415206ipimppacs    5/ 2121
593415206ipsecho    1/ 2121
593415206ippet    ./ 2121
593415206iptte  285/ 2121
593415206iptee   24/ 2121
593415206ipperfs   13/ 2121
593415206ipcts    4/ 2121
593415206ipcabgs    ./ 2121
593415206opcaths   29/ 2121
593415206oppcis    5/ 2121
593415206opimppacs    4/ 2121
593415206opsecho    6/ 2121
593415206oppet    ./ 2121
593415206optte  160/ 2121
593415206optee    7/ 2121
593415206opperfs   28/ 2121
593415206opcts    2/ 2121
593415206opcabgs    ./ 2121
593415206ofcrevascs    ./   78
593415206iprevascs   28/   78
593415206oprevascs    3/   78
593415206ofcoutptpci    ./   36
593415206ipoutptpci    3/   36
593415206opoutptpci    3/   36
593415206ofcminrestudy    ./   25
593415206ipminrestudy    3/   25
593415206opminrestudy    1/   25
593415206ofcmajrestudy    ./   25
593415206ipmajrestudy    4/   25
593415206opmajrestudy    ./   25
593415206ofcpcifu30    ./   32
593415206ippcifu30    3/   32
593415206oppcifu30    ./   32
593415206ofcpcifu182    ./   17
593415206ippcifu182    3/   17
593415206oppcifu182    ./   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 RATE3/17./17./17
1-30 DAY PCI REDO RATE3/32./32./32
3-MONTH MAJOR RESTUDY RATE4/25./25./25
3-MONTH MINOR RESTUDY RATE3/251/25./25
CABG RATE./2121./2121./2121
CARDIAC CT RATE4/21212/2121./2121
CATH RATE47/212129/21212/2121
IMPLANT RATE5/21214/2121./2121
OUTPATIENT PCI RATE3/363/36./36
PCI RATE31/21215/2121./2121
CARDIAC PET SCAN./2121./2121./2121
REVASC RATE28/783/78./78
SPECT/PERFUSION RATE13/212128/2121262/2121
STRESS ECHO RATE1/21216/212194/2121
TEE ECHO RATE24/21217/2121./2121
TTE ECHO RATE285/2121160/2121448/2121

Accepted Solutions
Solution
‎08-03-2013 10:49 AM
SAS Super FREQ
Posts: 8,742

Re: proc report to rtf problem

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;


report_without_transpose.png

View solution in original post


All Replies
Super Contributor
Posts: 307

Re: proc report to rtf problem

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;

Super Contributor
Posts: 339

Re: proc report to rtf problem

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

SAS Super FREQ
Posts: 8,742

Re: proc report to rtf problem


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

Solution
‎08-03-2013 10:49 AM
SAS Super FREQ
Posts: 8,742

Re: proc report to rtf problem

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;


report_without_transpose.png
Contributor
Posts: 69

Re: proc report to rtf problem

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 242 views
  • 0 likes
  • 4 in conversation