BookmarkSubscribeRSS Feed
Bill
Quartz | Level 8

Using the code below to produce an html file to be read by excel.  An example of the output I'm trying to create is in the attached capture.png.  The code below produces the column backgrounds, but I would also like to have row group backgrounds.  How is that accomplished?

Thank you,

Bill

proc tabulate
data=ChrgLossYldAll order=data style=[foreground=black font_face=
'arial
narrow'
font_size=12pt];

    format Process_Op_Cd $OpName. month monyy5. CorpCd $CorpNam.;

     class CorpCd Process_Op_Cd Month;

    classlev Process_Op_Cd;

         var MY ExitCoilScrapY TotalScrapY       
           YldPct ExitCoilScrapPct TotalScrapPct

                       ChrgWt ScrapWt TotalScrapWt

            /style=[background=cxfef0d9];

     var ToolScrapPct  ToolScrap

       QScrapWt QScrapPct  

       ToolScrapY QScrapY ;

          table CorpCd=''*process_op_cd='',

         (MY='Metal Yield (units/1000)'*f=9.0*[style=[background=cxfef0d9]]
         ToolScrapY=
'Tool Scrap (units/1000)'*f=9.2

          ExitCoilScrapY='Exit
         Scrap Coils (units/1000)'
*f=9.2*[style=[background=cxfef0d9]]
         QScrapY=
'Downgrade Scrap Coils (units/1000)'*f=9.2

                   TotalScrapY='Total Scrap Loss (units/1000)'*f=9.2*[style=[background=cxfef0d9]])*sum=''

          *month=' '/rts=25 ;


Capture.PNG
5 REPLIES 5
Cynthia_sas
SAS Super FREQ

Hi: You didn't post enough of a capture to explain what you meant by "row group backgrounds" -- do you mean the backgrounds for CorpCD and process_op_cd that seem to be cut off in your screen shot???

If you want to style the background for the LEVELS of CorpCD and process_op_cd, then you need to have a user-defined format and use the <parent> style for inheritance. See this example, using SASHELP.PRDSALE. The syntax you need will be

style=<parent> -- but I am not entirely sure that the forum posting system will allow me to post the < and > in code. So if the code looks messed up, the syntax is:

style=< parent > but without any spaces in the string.

Cynthia

 
proc format;
  value $ctry 'CANADA'='pink'
              'GERMANY'='yellow'
              'U.S.A.'='cyan';
run;
 
ods html file='c:\temp\row_bands.html' style=sasweb;
ods msoffice2k file='c:\temp\row_bands_ht.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\row_bands_xp.xml' style=sasweb;
proc tabulate data=sashelp.prdsale f=comma8.;
  title '1) Changing Style for Country and Division in Bands';
  title2 'The parent for DIVISION classlev is COUNTRY classlev';
  title3 'The COUNTRY classlev color is determined by the STYLE override';
 
  ** this will change COUNTRY and DIVISION levels to their respective colors;
  class country division/ style={background=green foreground=black};
  classlev country / style={background=$ctry. foreground=black};
  classlev division / style=<parent>;

  

  ** this will change all PRODTYPE levels to CYAN;
  class prodtype / style={background=cyan foreground=black};
  classlev prodtype /style=<parent>{foreground=black};
  var actual /style=<parent>{foreground=black};

   

  table country=' '*division=' ',
        prodtype*actual='Actual'*(sum='Total'  max='Max')
    / box={label="Banding Row Colors for Country and Division"};

run;
  
proc tabulate data=sashelp.prdsale f=comma8.;
  title '2) Changing Style for Country and Division in Bands';
  title2 'The parent for DIVISION classlev is COUNTRY classlev';
  title3 'The COUNTRY classlev color is determined by the STYLE override';
 
  ** this will change COUNTRY and DIVISION levels to their respective colors;
  class country division/ style={background=green foreground=black};
  classlev country / style={background=$ctry. foreground=black};
  classlev division / style=<parent>;

  

  ** this will change all PRODTYPE levels to CYAN;
  class prodtype / style={background=cyan foreground=black};
  classlev prodtype /style=<parent>{foreground=black};
  var actual /style=<parent>{foreground=black};

   

  ** note use of style=<parent> in row dimension;
  table country=' '*division=' '*{style=<parent>},
        prodtype*actual='Actual'*(sum='Total'  max='Max')
    / box={label="Banding Row Colors for Country and Division"} style_precedence=row;

run;
ods _all_ close;


banding_with_tabulate.png
Bill
Quartz | Level 8

Thanks Cynthia;

I'm a little further.  What I'm looking for is some way to have row and column banding.  I don't think that's quite possible, so I've tried to use font colour as the the vertical banding method in the example that you sent.  My changes are included below.  I'm not able to make the row bands extend across the entire row.  Please see the Capture.png file attached. I'd appreciate more of your great help.

Thank you,

Bill

proc format;

value $ctry 'GERMANY'='yellow'

             other='white';

run;



ods html file='c:\temp\row_bands.xls';


proc tabulate data=sashelp.prdsale f=comma8.;

title '2) Changing Style for Country and Division in Bands';

title2 'The parent for DIVISION classlev is COUNTRY classlev';

title3 'The COUNTRY classlev color is determined by the STYLE override';


* this will change COUNTRY and DIVISION levels to their respective colors;

class country division/ style={background=green foreground=black};

classlev country / style={background=$ctry. foreground=black};

classlev division / style=<parent>;


** this will change all PRODTYPE levels to CYAN;

class prodtype / style={background=cyan foreground=black};

classlev prodtype /style=<parent>{foreground=black};

var actual /style=<parent>{foreground=black};


** note use of style=<parent> in row dimension;

table country=' '*division=' '*{style=<parent>},

'Actual'*(sum='Total' *[style=[foreground=black]] max='Max'*[style=[foreground=green]])


box={label="Banding Row Colors for Country and Division"};* style_precedence=row;


run;


ods _all_ close;


Capture.PNG
Cynthia_sas
SAS Super FREQ

HI:

  Hmmmm, I don't see how your code is working at all. you should have a slash (/) BEFORE the BOX= option, so I don't understand how you got your screen shot. Also, why did you get rid of the style_precedence=row???? In addition, your screen shot shows PRODTYPE in the column dimension, but this code does NOT have PRODTYPE in the TABLE statement at all. Are you sure that the code you posted is the code that produced your screen shot? At any rate, if you do NOT have style_precedence=row, then you will NOT get banding across the entire row, including the data cells. PROC TABULATE only has 1 dimension that can "win" when conflicting style elements are specified for multiple dimensions.

  PROC REPORT, on the other hand, could make the MAX column green and still do the yellow banding that you want. Have you ever used PROC REPORT before?

  Also, I notice that you used ODS HTML instead of ODS MSOFFICE2K, as I showed in my code snippet. The reason I used ODS MSOFFICE2K is that MSOFFICE2K is "Microsoft friendly" HTML -- it is the HTML standard invented by Microsoft when they disagreed with the W3C over the HTML 4.0 specification. If you use ODS MSOFFICE2K as the destination, the resulting HTML tags will be "Microsoft friendly" tags. In the same vein, if you use ODS TAGSETS.EXCELXP, you will create Microsoft-friendly XML that Excel knows how to open and render to create multi-sheet workbooks.

cynthia

Bill
Quartz | Level 8

Thanks Cynthia;

It appears that I was somewhat too generous in cutting code for posting ...

I removed the style_precedence=row in attempt to eliminate the "win" that was happening. Now knowing the constraint that Proc Tabulate can only "win" in one direction now becomes the issue.  I'll have to learn how to use proc report.

Thanks for the tip about MSOffice2k anf the tagsets.  I'll have to investigate that too.

Thank you for your help,

Bill

Cynthia_sas
SAS Super FREQ

HI, Bill:

  Here's something to get you started and a little "light" reading on the topic of PROC REPORT.

http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Cynthia

      

proc format;
value $ctry 'GERMANY'='yellow'
             other='white';
run;
    
ods html file='c:\temp\report_bands.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\report_bands.xml' style=sasweb;
ods msoffice2k file='c:\temp\report_bands.xls' style=sasweb;
proc report data=sashelp.prdsale nowd;
where prodtype in ('FURNITURE', 'OFFICE');
title '1a) Using PROC REPORT';

  column country rowcolor division prodtype,(actual actual=summax);
  define country / group;
  define rowcolor / computed /* noprint */;
  define division / group;
  define prodtype / across;
  define actual / sum 'Sum';
  define summax / max 'Max' style(column)={foreground=green font_weight=bold};
  break before country / ;
  compute country;
    length holdctry $10;
    if upcase(_break_) = 'COUNTRY' then
       holdctry = country;
  endcomp;
  compute rowcolor / character length=100;
    stylestr = catt('style={background=',put(holdctry,$ctry.),'}');
    rowcolor = stylestr;
    call define (_row_,'style',stylestr);
  endcomp;
run;

     

proc report data=sashelp.prdsale nowd;
where prodtype in ('FURNITURE', 'OFFICE');
title '1b) Using PROC REPORT';

  column country rowcolor division prodtype,(actual actual=summax);
  define country / group;
  define rowcolor / computed   noprint  ;
  define division / group;
  define prodtype / across;
  define actual / sum 'Sum';
  define summax / max 'Max' style(column)={foreground=green font_weight=bold};
  break before country / ;
  compute country;
    length holdctry $10;
    if upcase(_break_) = 'COUNTRY' then
       holdctry = country;
  endcomp;
  compute rowcolor / character length=100;
    stylestr = catt('style={background=',put(holdctry,$ctry.),'}');
    rowcolor = stylestr;
    call define (_row_,'style',stylestr);
  endcomp;
run;
ods _all_ close;

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
  • 5 replies
  • 2222 views
  • 6 likes
  • 2 in conversation