topic Re: PROC REPORT STYLE IN HEADER in ODS and Base Reporting

PROC REPORT STYLE IN HEADER

P_S_ — Fri, 31 May 2013 16:30:42 GMT

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;

Re: PROC REPORT STYLE IN HEADER

Cynthia_sas — Fri, 31 May 2013 16:52:49 GMT

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

Re: PROC REPORT STYLE IN HEADER

P_S_ — Fri, 31 May 2013 18:16:27 GMT

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;

Re: PROC REPORT STYLE IN HEADER

Cynthia_sas — Fri, 31 May 2013 22:44:35 GMT

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;

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;

Re: PROC REPORT STYLE IN HEADER

P_S_ — Sat, 01 Jun 2013 03:20:00 GMT

This is great. Thanks alot for your help.

Re: PROC REPORT STYLE IN HEADER

dataMart87 — Fri, 08 Nov 2013 23:29:20 GMT

Hi,

Do you know if METHOD2 is fixed in 9.4? 'just=L' does not seem to work either in 9.3.

Re: PROC REPORT STYLE IN HEADER

Cynthia_sas — Sat, 09 Nov 2013 17:22:32 GMT

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;