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;
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:
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
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.
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
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:
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
Hi Cynthia,
That works perfectly. Thank you so much for your thorough response.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.