Hi All,
I need some help the the style option in PROC REPORT. The code I am using produces the first table (labelled as CURRENT OUTPUT). You will notice that the text 'MY TITLE' above TOTAL SALES and MEAN SALES does not have the green background that I need. Ideally I would want the output shown in the 2nd table (labelled as OUTPUT I NEED).
Below is the code I have used. Any help is appreciated.
Thank you.
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' out=abc;
column region country product,actual ('MY TITLE' totalsales totalsales_mean);
define region / group;
define country / group;
define product / across;
define actual / analysis sum
format=dollar8.
'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
Hi:
I no longer have 9.1.3 to test with. So if you have trouble with the code below, you'll have to work with Tech Support. I tested with SAS 9.3 and Excel 2010.
Sadly, Method 1 has a downside, which will become apparent when you run the code. Because you have multiple header levels for PRODUCT, you get extra cells above REGION and COUNTRY -- you cannot "touch" those cells to get rid of the green in Method 1. Also, Method 2 works for some destinations, but not for ExcelXP. Using ESCAPECHAR in 9.3, I can change the foreground color of the cell, but not the background color of the "My Title" cell for the spanning header. So Method 2 is a bust for ExcelXP. Method 3 (not mentioned previously) is to make a "fake across header variable" that you can use in the COLUMN statement and then you will have a DEFINE statement on which to change the color of that cell. All 3 methods are illustrated by running the code below. As I said, I tested in 9.3 because I do not have 9.2 to run with. Also, it was a --LONG-- time ago, but I remember that at one point in time ExcelXP had an issue with the MINIMAL style...don't actually remember which version of SAS that was, so it could have been 8 or 9.0 -- but I throw that out for a warning. (Again, another reason to work with Tech Support.)
cynthia
title;
options obs=max leftmargin=0.2in rightmargin=0.2in topmargin=0.2in bottommargin=0.2in;
ods tagsets.excelxp file = "c:\temp\all_three.xml" style = minimal
options(orientation ='landscape' doc='help' embedded_titles='yes' sheet_name='Method1');
**Method 1 -- not desirable because cannot "touch" upper left hand cells;
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' style(header)=[background = CXC2D69A] ;
title '1) change ALL headers in PROC REPORT stmt';
title2 'works best when only 1 level of header';
column region country product,actual ("My Title" totalsales totalsales_mean);
define region / group style(header)=[background = _undef_] ;
define country / group style(header)=[background = _undef_] ;
define product / across style(header)=[background = _undef_] ;
define actual / analysis sum style(header)=[background = _undef_]
format=dollar8. 'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
** method 2- ESCAPECHAR change bkground not work for ExcelXP but foreground does work (in 9.3);
ods escapechar='^';
ods tagsets.excelxp options(sheet_name='Method2');
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' ;
title '2) Method 2 ignored by ODS EXCELXP';
title2 'and results are not best because header "grows"';
column region country product,actual ("^S={foreground=red background=cxc2d69a}My Title" totalsales totalsales_mean);
define region / group ;
define country / group ;
define product / across ;
define actual / analysis sum
format=dollar8. 'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods tagsets.excelxp options(sheet_name='Method3');
** method 3 - -when method 2 (escapechar) does not work for dest;
data makefakehdr;
set sashelp.prdsale(where=(prodtype='OFFICE'));
fake_hdr = 'My Title';
run;
proc report data=makefakehdr(where=(prodtype='OFFICE'))
nowd split='*' ;
title '3) Use "fake_hdr" var as an across var';
title2 'then you have DEFINE to change color but also have to change compute block';
column region country product,actual fake_hdr,(totalsales totalsales_mean);
define region / group ;
define country / group ;
define product / across ;
define actual / analysis sum style(header)=[background = _undef_]
format=dollar8. 'Sales';
define fake_hdr/ across style(header)={background = CXC2D69A} ' ';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
_c6_ = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
_c7_ = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods tagsets.excelxp close;
title;
Hi, You do not show your destination of interest. Usually, when you have spanning headers like this, you either 1) change all the headers to the color you want, which includes spanning headers and then change the others (like product, region, country back to the "basic" color or 2) you use ODS ESCAPECHAR with a STYLE= override. The issue with the #2 method is that it is interpreted differently in different destinations. So, for example, you might see the whole cell background as green in one destination, but only the background under the letters as green in another destination. This is why knowing what destination (ODS statements) you are using would be helpful.
This question has been posted in the forums before, too. Did you search the forum for similar posts?
cynthia
Hi Cynthia, Thank you for the comments.
I am using excelxp tagsets to output the results into excel spreadsheet. Can you please elaborate on both the options that you have suggested and show me how I can accomplish them.
I have looked around abit for the previous posts but I haven't had much luck. I will search for more.
In any case the full code is shown below. I am using SAS 9.1.3. Thanks again.
options obs=100 leftmargin=0.2in rightmargin=0.2in topmargin=0.2in bottommargin=0.2in;
ods tagsets.excelxp
file = "~/test.xls"
style = minimal
options (orientation ='landscape');
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' out=abc;
column region country product,actual ('MY TITLE' totalsales totalsales_mean);
define region / group;
define country / group;
define product / across;
define actual / analysis sum
format=dollar8.
'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods tagsets.excelxp close;
Hi:
I no longer have 9.1.3 to test with. So if you have trouble with the code below, you'll have to work with Tech Support. I tested with SAS 9.3 and Excel 2010.
Sadly, Method 1 has a downside, which will become apparent when you run the code. Because you have multiple header levels for PRODUCT, you get extra cells above REGION and COUNTRY -- you cannot "touch" those cells to get rid of the green in Method 1. Also, Method 2 works for some destinations, but not for ExcelXP. Using ESCAPECHAR in 9.3, I can change the foreground color of the cell, but not the background color of the "My Title" cell for the spanning header. So Method 2 is a bust for ExcelXP. Method 3 (not mentioned previously) is to make a "fake across header variable" that you can use in the COLUMN statement and then you will have a DEFINE statement on which to change the color of that cell. All 3 methods are illustrated by running the code below. As I said, I tested in 9.3 because I do not have 9.2 to run with. Also, it was a --LONG-- time ago, but I remember that at one point in time ExcelXP had an issue with the MINIMAL style...don't actually remember which version of SAS that was, so it could have been 8 or 9.0 -- but I throw that out for a warning. (Again, another reason to work with Tech Support.)
cynthia
title;
options obs=max leftmargin=0.2in rightmargin=0.2in topmargin=0.2in bottommargin=0.2in;
ods tagsets.excelxp file = "c:\temp\all_three.xml" style = minimal
options(orientation ='landscape' doc='help' embedded_titles='yes' sheet_name='Method1');
**Method 1 -- not desirable because cannot "touch" upper left hand cells;
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' style(header)=[background = CXC2D69A] ;
title '1) change ALL headers in PROC REPORT stmt';
title2 'works best when only 1 level of header';
column region country product,actual ("My Title" totalsales totalsales_mean);
define region / group style(header)=[background = _undef_] ;
define country / group style(header)=[background = _undef_] ;
define product / across style(header)=[background = _undef_] ;
define actual / analysis sum style(header)=[background = _undef_]
format=dollar8. 'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
** method 2- ESCAPECHAR change bkground not work for ExcelXP but foreground does work (in 9.3);
ods escapechar='^';
ods tagsets.excelxp options(sheet_name='Method2');
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' ;
title '2) Method 2 ignored by ODS EXCELXP';
title2 'and results are not best because header "grows"';
column region country product,actual ("^S={foreground=red background=cxc2d69a}My Title" totalsales totalsales_mean);
define region / group ;
define country / group ;
define product / across ;
define actual / analysis sum
format=dollar8. 'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods tagsets.excelxp options(sheet_name='Method3');
** method 3 - -when method 2 (escapechar) does not work for dest;
data makefakehdr;
set sashelp.prdsale(where=(prodtype='OFFICE'));
fake_hdr = 'My Title';
run;
proc report data=makefakehdr(where=(prodtype='OFFICE'))
nowd split='*' ;
title '3) Use "fake_hdr" var as an across var';
title2 'then you have DEFINE to change color but also have to change compute block';
column region country product,actual fake_hdr,(totalsales totalsales_mean);
define region / group ;
define country / group ;
define product / across ;
define actual / analysis sum style(header)=[background = _undef_]
format=dollar8. 'Sales';
define fake_hdr/ across style(header)={background = CXC2D69A} ' ';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
_c6_ = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
_c7_ = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods tagsets.excelxp close;
title;
This is great. Thanks alot for your help.
Hi,
Do you know if METHOD2 is fixed in 9.4? 'just=L' does not seem to work either in 9.3.
Hi,
It's not a good idea to piggyback a new question onto an already answered question. It would be better to start a new post and refer back to this post.
The bottom line is that Method 2 with ODS ESCAPECHAR wasn't "broken". It works in some destinations and doesn't work in others. When ODS makes output, it has to make output that conforms to the rules of the destination. So it is the application that -RENDERS- the output that is determining how the style controls will be interpreted. ExcelXP and HTML interpret ODS ESCAPECHAR background color instructions differently than RTF and PDF. There are inherent differences in the destinations. If you run the Method 2 code below, you will see the inherent differences in the output files that are created.
When I run the attached code in 9.4 for Method 2, the results are exactly what I expect. The cell with "My Title" gets a background color of green in RTF and PDF destinations, gets no background color in ExcelXP and in the HTML output gets a background only under the letters, not in the whole cell. This is exactly what I expect. However, since Method 3 works in TAGSETS.EXCELXP,as well as other destinations, then the Method 3 code, as written above is the method you would use.
cynthia
title;
options obs=max leftmargin=0.2in rightmargin=0.2in topmargin=0.2in bottommargin=0.2in;
ods rtf file='c:\temp\only_method2.rtf' style=minimal;
ods pdf file='c:\temp\only_method2.pdf' style=minimal;
ods html file='c:\temp\only_method2_ht.html' style=minimal;
ods tagsets.excelxp file = "c:\temp\only_method2_xp.xml" style = minimal
options(orientation ='landscape' doc='help' embedded_titles='yes' sheet_name='Method1');
** method 2- change bkground not work for ExcelXP but foreground does work (in 9.3);
ods escapechar='^';
ods tagsets.excelxp options(sheet_name='Method2');
proc report data=sashelp.prdsale(where=(prodtype='OFFICE'))
nowd split='*' ;
title '2) Method 2 ignored by Excel when output created with ODS EXCELXP';
column region country product,actual ("^S={foreground=red background=cxc2d69a}My Title" totalsales totalsales_mean);
define region / group ;
define country / group ;
define product / across ;
define actual / analysis sum
format=dollar8. 'Sales';
define totalsales / computed format=dollar10.
'Total Sales' style(header)=[background = CXC2D69A] ;
define totalsales_mean / computed format=dollar10.
'Mean Sales' style(header)=[background = CXC2D69A] ;
break after region / summarize suppress;
rbreak after / summarize;
compute totalsales;
totalsales = sum(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
compute totalsales_mean;
totalsales_mean = mean(_c3_, _c4_, _c5_);
if _break_ = '_RBREAK_' then call define(_col_,'style','style=[background=CXC2D69A]');
endcomp;
run;
ods _all_ close;
title;
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 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: