BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cfoo
Calcite | Level 5

 Is there a simple way to transpose the output of proc report in version 9.4?

 

I have a simple proc report output that I have to generate several times for different individuals. It's a table with 4 columns and 2 rows. However, for the sake of fitting it nicely into my report, I would like to know if there is a way I can manipulate proc report to transpose this table to be 2 columns and 4 rows, like the attached photo. 

 

 

 

 

DATA have;
input rate pooled goal cad sig;
datalines ;
0.71 1.09 0.70 1.2 3
;
run;

 


proc report data=have missing nowd headskip headline split='\'
/*Set the color/text style elements for the table*/
style(report)={rules=none frame=hsides borderspacing=0}
style(header)={borderwidth=0 borderspacing=0 background=CXE1E1E1 foreground=black font_face=arial font_weight=bold font_size=9pt }
style(column)={borderwidth=0 borderspacing=0 background=CXEDEDED foreground=black font_face=arial font_size=9pt };
/*Identify the variables that will appear/are referenced in the table, and create any headers that span more than one variable*/
column sig rate pooled goal cad ;
/*Create header labels for each variable, set column widths and alignment*/
define rate / order center "Facility SIR" style(column)=[width=.72in vjust=middle];
define pooled / display center "Group Pooled SIR" style(column)=[width=.8in vjust=middle] ;
define goal / display "HHS 2020 Goal" center style(column)=[width=.72in vjust=middle];
define cad / display "Need to Prevent" center style(column)=[width=.72in vjust=middle];
define sig / display noprint;
compute rate;
if sig="1"
then call define(_col_,"style", "style=[font_size=10pt fontweight=bold color=gray]");
else if sig="2"
then call define(_col_,"style", "style=[font_size=10pt fontweight=bold color=red]");
else if sig="3"
then call define(_col_,"style", "style=[font_size=10pt fontweight=bold color=green]");
else if sig="4"
then call define(_col_,"style", "style=[font_size=10pt fontweight=bold color=black]");
endcomp;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  You didn't mention what your desired output is -- PDF, RTF, HTML, Excel? It makes a difference. In your output, you don't have column headers, at all -- is that what you want? Two columns but nothing about what the headers are?

 

  Do you want the rows alternating colors, as you show in your output. Or do you want the headers in one color and the ALL the column in the second color (which is what you have coded now).

 

  No title to the report? Also, a few comments HEADLINE and HEADSKIP are ignored by ODS and so aren't doing anything in the output  here, they will be ignored. 

 

  In order to do what you want with PROC REPORT, you'll need to restructure your data. Instead of 1 obs with all your variables, you'll have to make 1 row for every value -- you show 4 rows in your desired output, so you'll need 4 obs. But it is possible. This was produced with PROC REPORT:

with_report_restruct.png

 

shown here without headers and with alternate row background.

 

To restructure the data, you'd have to do something like this:

DATA have_orig(keep=rate pooled goal cad sig)
     have_report(keep=ord sig col1 col2);
length col1 $30 col2 8 ;
input rate pooled goal cad sig;
output have_orig;

ord=1;
col1 = 'Facility';
col2 = rate;
output have_report;

ord=2;
col1 = 'Group Pooled SIR';
col2 = pooled;
output have_report;

ord=3;
col1='HHS 2020 Goal';
col2 = goal;
output have_report;

ord=4;
col1='Need to Prevent';
col2=cad;
output have_report;
datalines ;
0.71 1.09 0.70 1.2 3
;
run;

 

And then the report code would be fairly simple, I used a format instead of IF statements for the coloring of RATE:


proc format;
  value sigcolr 1='gray'
                2='red'
                3='green'
                4='black';
run;

ods pdf file='c:\temp\alt_report.pdf' style=pearl;
ods rtf file='c:\temp\alt_report.rtf' style=rtf;
ods html path='c:\temp'(url=none) file='alt_report.html' style=journal;
 
proc report data=have_report noheader
  style(report)={rules=none frame=hsides borderspacing=0}
  style(column)=[font_size=10pt fontweight=bold];
  column ord sig col1 col2;
  define ord / order noprint;
  define sig / display noprint;
  define col1 / display 'Type';
  define col2 / display 'Value';
  compute ord;
    ** use this compute block if want alternating row colors;
    x = mod(ord,2);
    if x = 0 then call define(_row_,'style','style={background=CXEDEDED}');
    else call define(_row_,'style','style={background=cxe1e1e1}');
  endcomp;
  compute col2;
    length svar $100 ;
    ** only want to change the color for rate, which is ord=1;
    if ord=1 then do;
       svar = catt('style={color=',put(sig,sigcolr.),'}');
       call define(_col_,'style',svar);
    end;
  endcomp;
run;
ods _all_ close;


If you want alternate row coloring then keep the COMPUTE block for ORD, Otherwise, get rid of it. I didn't bother changing the cell width. It didn't seem necessary.

 

Hope this helps,

 

Cynthia

View solution in original post

4 REPLIES 4
Reeza
Super User

I would recommend doing that manually into a data set and then using PROC PRINT or REPORT to display the final table. Since your units will likely have different formats it usually requires character variables so PROC PRINT works better.

 

 

pau13rown
Lapis Lazuli | Level 10

one thing you could do without modifying the dataset is simply "options orientation=landscape" to make the table fit on the page. But otherwise, i think you're required to manipulate the dataset (tranpose it etc) in preparation for the proc report

Cynthia_sas
SAS Super FREQ

Hi:

  You didn't mention what your desired output is -- PDF, RTF, HTML, Excel? It makes a difference. In your output, you don't have column headers, at all -- is that what you want? Two columns but nothing about what the headers are?

 

  Do you want the rows alternating colors, as you show in your output. Or do you want the headers in one color and the ALL the column in the second color (which is what you have coded now).

 

  No title to the report? Also, a few comments HEADLINE and HEADSKIP are ignored by ODS and so aren't doing anything in the output  here, they will be ignored. 

 

  In order to do what you want with PROC REPORT, you'll need to restructure your data. Instead of 1 obs with all your variables, you'll have to make 1 row for every value -- you show 4 rows in your desired output, so you'll need 4 obs. But it is possible. This was produced with PROC REPORT:

with_report_restruct.png

 

shown here without headers and with alternate row background.

 

To restructure the data, you'd have to do something like this:

DATA have_orig(keep=rate pooled goal cad sig)
     have_report(keep=ord sig col1 col2);
length col1 $30 col2 8 ;
input rate pooled goal cad sig;
output have_orig;

ord=1;
col1 = 'Facility';
col2 = rate;
output have_report;

ord=2;
col1 = 'Group Pooled SIR';
col2 = pooled;
output have_report;

ord=3;
col1='HHS 2020 Goal';
col2 = goal;
output have_report;

ord=4;
col1='Need to Prevent';
col2=cad;
output have_report;
datalines ;
0.71 1.09 0.70 1.2 3
;
run;

 

And then the report code would be fairly simple, I used a format instead of IF statements for the coloring of RATE:


proc format;
  value sigcolr 1='gray'
                2='red'
                3='green'
                4='black';
run;

ods pdf file='c:\temp\alt_report.pdf' style=pearl;
ods rtf file='c:\temp\alt_report.rtf' style=rtf;
ods html path='c:\temp'(url=none) file='alt_report.html' style=journal;
 
proc report data=have_report noheader
  style(report)={rules=none frame=hsides borderspacing=0}
  style(column)=[font_size=10pt fontweight=bold];
  column ord sig col1 col2;
  define ord / order noprint;
  define sig / display noprint;
  define col1 / display 'Type';
  define col2 / display 'Value';
  compute ord;
    ** use this compute block if want alternating row colors;
    x = mod(ord,2);
    if x = 0 then call define(_row_,'style','style={background=CXEDEDED}');
    else call define(_row_,'style','style={background=cxe1e1e1}');
  endcomp;
  compute col2;
    length svar $100 ;
    ** only want to change the color for rate, which is ord=1;
    if ord=1 then do;
       svar = catt('style={color=',put(sig,sigcolr.),'}');
       call define(_col_,'style',svar);
    end;
  endcomp;
run;
ods _all_ close;


If you want alternate row coloring then keep the COMPUTE block for ORD, Otherwise, get rid of it. I didn't bother changing the cell width. It didn't seem necessary.

 

Hope this helps,

 

Cynthia

cfoo
Calcite | Level 5

Hi Cynthia,

That works perfectly. Thank you so much for your thorough response. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 2100 views
  • 0 likes
  • 4 in conversation