How to decorate line in Proc Report?

[GPatel]

Dear SAS Communities Members :

I have attached sample code below.

How can I change background/font type/font size  of line # 1 (Make : Acura)  to yellow,

and insert a blank thin line   and

change background/font type/font size of line # 3 (Type : SUV) to lightblue ?

proc report data=sashelp.cars(obs=25 keep=Make

Model

Type

Origin

DriveTrain

MSRP

Invoice

) nowd headline headskip

style(report)=[cellspacing=5 borderwidth=0 bordercolor=white]

style(header)=[color=Black

               fontstyle=italic fontsize=4]

style(column)=[color=moderate brown

               fontfamily=helvetica fontsize=4]

style(lines)=[color=white backgroundcolor=black

              fontstyle=italic fontweight=bold fontsize=5]

style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9

                fontfamily=helvetica fontsize=3 textalign=r]

style(calldef)=[background=white];

Columns Make model

Type

Origin

DriveTrain

MSRP

Invoice

;

Define make / Order Noprint;

Define model / 'Model'   ;

  Define type / Order Noprint;

  Define origin / Order Noprint;

  Define DriveTrain / Order Noprint;

Define	MSRP / sum analysis ;
Define	invoice / sum analysis ;

break after type / page skip;

compute before _page_ / left   style=[font_weight=bold foreground=green background=yellow];

if  make  ne ' '  then  call define(_lines_,'style', 'style=[ foreground=green background=yellow  fontstyle=italic fontsize=2 ]');

  line 'Make: ' Make $char.  ;

  line ' ';

if Type ne ' '  then  call define(_row_,'style', 'style=[ foreground=red background=silver  fontstyle=italic fontsize=2  ]');

line 'Type : ' Type  $char.;

endcomp;

run;

[Cynthia_sas]

Hi:

  Can you point me to the place in the documentation where you found this syntax for CALL DEFINE???

call define(_lines_,'style', ' ...');

As far as I know, this is incorrect syntax and I could not find it in the documentation.

Also, if you are using ODS, then your HEADLINE and HEADSKIP are entirely unnecessary and ignored by ODS destinations.

Last, how you achieve a background color of yellow will be the same for all destinations, but how to achieve underlining depends on what version of SAS you have (9.1.3, 9.2, 9.3, etc) and on your destination. Since you don't show your destination, or give your version of SAS, it's hard to help.

Also, although you showed code that used SASHELP.CARS (thanks for that), you didn't post working code (your code should show errors in the log) and you did not show ALL your code -- what is your ODS DESTINATION of choice? HTML, RTF, PDF???

Thanks, cynthia

PS...your COMPUTE BEFORE _PAGE_ is creating 1 box at the top of each table on each "page" -- for some destinations, that box can only be 1 color. 1 box=1 background. Each LINE statement can have different text, but ALL the line statements in your COMPUTE block are going into 1 box. Some destinations might allow you to have a different background for each "line statement", but generally, this turns out not looking the way you think it will. And, as I said, you didn't mention your destination of interest.

[GPatel]

Cynthia:

Thanks for your prompt reply and comments.  SAS code (Version SAS 9.4) updated is attached below for your review and reference. 

Is it possible to design report with following changes ?

(1) For the second line, it should be ' THIN ' line with white background.

(2) Background of third line (which reads -: Type : Sedan) should be other than yellow.

(3) Font Color  for the third line should be black.

%let ff = %str(HEIGHT=9PT Bold F=ARIAL bcolor="White" color='BLACK') ;

  %Let Title1 =%str(Line of Business : Electronics);

  %Let Title2 =%str(US Export);

  %Let Title3 =%str(Enterprise Unit);

  %Let Title4 =%str(Report ID: Ele_Export_US);

  %Let Title5 =%str(Department : HR01AL9012) ;

  %Let Title6 =%str(Paid Period);

  %Let Title7 =%str( December 2013);

     Title  Font=Arial bold height=12pt

      J=c "&title1"

      J=c Font=Arial bold height=12pt  "&title2" 

      J=l Font=Arial bold height=10pt  "&title3"

      J=l Font=Arial bold height=10pt  "&title4"

      J=c Font=Arial bold height=9pt   "^{newline 1}"  "&title5"

      J=c Font=Arial bold height=9pt   "^{newline 1}"  "&title6"

      J=c Font=Arial bold height=9pt   "^{newline 1}"  "&title7";

   FootNote ' ';

  ODS escapechar='^';     ODS _all_ close; 

  options orientation=landscape leftmargin="0.2cm" rightmargin="0.2cm" Device=ACTXIMG

           topmargin="0.5cm" bottommargin="0.5cm" nodate nonumber  center   spool printerpath="Postscript";  

  ODS pdf file="d:\temp\Test.pdf" style=sasweb startpage=yes notoc;

  %Let starttime=%sysfunc(datetime());

  Proc Report Data=sashelp.cars(obs=5 keep=Make Model Type Origin MSRP Invoice) nowd

  style(report)=[cellspacing=5 borderwidth=0 bordercolor=white]

  style(header)=[color=Black

                Fontstyle=italic Fontsize=4]

  style(column)=[color=moderate brown

                Fontfamily=helvetica Fontsize=4]

  style(lines)=[color=black backgroundcolor=black

               Fontstyle=italic Fontweight=bold Fontsize=5]

  style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9

                 Fontfamily=helvetica Fontsize=3 textalign=r];

  /* style(calldef)=[background=white]; /* Reference from SAS Guide to Report Writing  By Michele M. Burlew : Page 191 */

  Columns Make model Type Origin MSRP Invoice  ;

  Define make / Order Noprint;

  Define model / 'Model'   ;

  Define type / Order Noprint;

  Define origin / Order Noprint; 

  Define MSRP / sum analysis ;

  Define invoice / sum analysis ;

  break after type / page skip;

  compute before _page_ / left   style=[Font_weight=bold foreground=green background=yellow]; 

  if  _Break_  ne ' '  then  CALL DEFINE(_COL_,'style', 'style=[ foreground=green background=yellow  Fontstyle=italic Fontsize=2 ]');

   line 'Make: ' Make $char.  ; /* First Line */

   line ' ';   /* Blank Line */

   if _Break_  ne ' '   then  CALL DEFINE(_COL_,'style', 'style=[ foreground=red background=silver  Fontstyle=italic Fontsize=2  ]');

   line 'Type : ' Type  $char.;  /* Third Line */

  endcomp;

  run;    

  ODS PDF Close;

  ODS LISTING ;

[Cynthia_sas]

Hi:

  Thanks for posting an update to your code. Some points..and a code example to test your basic question of can a single cell from COMPUTE BEFORE have 2 background colors?

1) This statement is what is making your background yellow and foreground green:

  compute before _page_ / left  style=[Font_weight=bold foreground=green background=yellow];

These 2 statements are totally being ignored:

  if _Break_  ne ' '   then  CALL DEFINE(_COL_,'style', 'style=[ foreground=red background=silver  Fontstyle=italic Fontsize=2  ]');

  if  _Break_  ne ' '  then  CALL DEFINE(_COL_,'style', 'style=[ foreground=green background=yellow  Fontstyle=italic Fontsize=2 ]');

  Generally, _COL_ is used to change the attributes of 1 cell. Your 2 statements are coded as though you think you have 2 different cells, but you don't. When you use the COMPUTE BEFORE _PAGE_, you are putting a gigantic cell a single cell above the whole table. And the if _BREAK_ ne ' ' test is really not testing for anything. The COMPUTE BEFORE _PAGE_ will only execute one time at the beginning of each page.

You can prove this to yourself by changing the COMPUTE block to:

   compute before _page_ / left  style=[Font_weight=bold foreground=purple background=pink];

you will now see pink and purple being used instead of yellow and green and you will not see red and silver anywhere. Your CALL DEFINE just won't work.

2) So if it is the COMPUTE block style override that is touching the LINES, then that means that this override in the PROC statement is totally being ignored

style(lines)=[color=black backgroundcolor=black

               Fontstyle=italic Fontweight=bold Fontsize=5]

and it doesn't make sense anyway for the foreground and the background to both be BLACK. STYLE(LINES) applies to the TEXT that you write, not to anything else, not to actual underlines or border lines.  So you may as well remove this override from the PROC statement. Or else take off the color= and backgroundcolor=, they are irrelevant to what you are trying to do.

3) You said that you wanted "For the second line, it should be ' THIN ' line with white background." What second line? -- your second LINE statement is writing out 1 single space. If you underline that space, it will be with the same thickness of underline as any other underline. And what you would underline would be 1 space -- IF your destination even respected the single space for an underline. Some will, some won't. You might have to switch to a non-breaking space, and even then, I'm not sure that PDF will "respect" the underline for a non-breaking space..

4) then you said "Background of third line (which reads -: Type : Sedan) should be other than yellow." and "Font Color  for the third line should be black" However, as I put in my PS to the first post. -- when you have a COMPUTE BLOCK, you are making 1 single cell that spans the whole table. You can put 1 LINE statement of text in the cell. You can put 10 LINE statements of text in the cell. It is 1 cell. You can't make the background of 1 line yellow and the background of 1 line green. A single cell has a single background. The TEXT in the cell can have a different foreground color -- so the first line could have green text and the third line could have red text, but you can't have 2 different backgrounds in PDF. I believe that PDF will use the first color...so your COMPUTE BEFORE block will produce 1 cell with 1 background color. Each line can have a different foreground color, but you will NOT change it with CALL DEFINE. You will have to use ODS ESCAPECHAR style overrides, similar to what you did

5) You are using "relative font sizes" in your code. every time you do that, you are making ODS PDF do the work of translating from the relative numbers (used most often in HTML output) to absolute PT numbers (used most often in PDF output). It would be nicer to just specify  Fontsize=12pt or Fontsize=10pt or Fontsize=18pt in your overrides instead of making PDF do the calculations from relative to absolute. And since you use absolute numbers in your TITLE, it doesn't make sense why you use relative numbers in your report.

6) Skip (like headline and headskip) is a "LISTING" only option. Totally ignored by ODS PDF -- unneeded here. And irrelevant...since your action on the break is to do a page break, the skip would even be ignored in LISTING.

7) I don't see any benefit to having just a single TITLE statement. SAS allows you to have 10 TITLE statements. Why not just use them. You only seem to have 7 text strings

😎 you have DEVICE=ACTXIMG -- but you do not have a SAS/GRAPH or PROC SGPLOT or a STAT procedure step. Not needed unless you have a graphics step.

9) You have startpage=yes as an option -- this is the default. Don't know why you have this.

  So let's take a step back from even trying to change the colors based on the MAKE and TYPE variables and just see what's possible with each destination. Here's a simple PROC REPORT where ALL we are dealing with is the COMPUTE BEFORE block and seeing how/whether/if a destination like PDF will even respect multiple background colors in a cell. Multiple foreground colors is very possible. But before you jump into the complex usage, let's test the concept with a simpler example.

  You should be able to run the code below. See whether what comes out of PDF is what you want. I don't think it is and I think my code shows that you can't "switch" backgrounds in mid-cell using your original technique. There is a way to make 2 cells -- one for your first line and one for your third line. However, the downside of this technique is that you won't be able to change the color and text of the "Third Line" (such as when TYPE changes) without "macro-izing" your code.

Cynthia

ods _all_ close;
options nodate nonumber;
title; footnote;
   
ods html file='c:\temp\show_compute_before.html';
ods rtf file='c:\temp\show_compute_before.rtf';
ods pdf file='c:\temp\show_compute_before.pdf';
ods escapechar='^';
 
** only some of the attributes in the STYLE(LINES) override will be used;
proc report data=sashelp.class nowd
  style(lines)={textdecoration=underline fontsize=14pt fontstyle=italic
                background=pink foreground=purple fontweight=bold};
  column name age sex height weight;
  compute before _page_ / left   style=[Font_weight=bold foreground=green background=yellow]; 
     ** colors in COMPUTE statement will override what is in PROC REPORT statement;
     ** colors in ESCAPECHAR style override will override what is in COMPUTE statement;
     line '^{style[color=cyan background=cxdddddd]First Line}'    ;
     line '^{style[textdecoration=underline asis=on]^{nbspace 1}}';   /* Non-breaking space will have 1 underscore in some dest*/
     line '^{style[color=black background=lightgreen]Third Line}'    ;  
  endcomp;
run;
    
ods _all_ close;
title;

ods html file='c:\temp\show_compute_alternate.html';
ods rtf file='c:\temp\show_compute_alternate.rtf';
ods pdf file='c:\temp\show_compute_alternate.pdf';
ods escapechar='^';
   
** the issue with this alternate approach is that you cannot change the text;
** "Third Line" without "macro-izing" your code. Right now, "Third Line" will be a fixed string.;
proc report data=sashelp.class nowd
  style(header)={background=lightgreen color=black}
  style(lines)={textdecoration=underline fontsize=14pt fontstyle=italic
                background=pink foreground=purple fontweight=bold};
  column ("^{style[just=l textdecoration=underline]Third Line}" name age sex height weight);
  define name / style(header)={background=white foreground=black};
  define age / style(header)={background=cxeeeeee foreground=red};
  define sex / style(header)={background=cxcccccc foreground=navy};
  define height / style(header)={background=cxaaaaaa foreground=lightgreen};
  define weight / style(header)={background=cx888888 foreground=cyan};
  compute before _page_ / left   style=[Font_weight=bold foreground=green background=yellow]; 
     ** colors in COMPUTE statement will override what is in PROC REPORT statement;
     line 'First Line'    ;
  endcomp;
run;
    
ods _all_ close;
title;

[GPatel]

Cynthia,

Thanks for your time and interest. I appreciate your efforts on this posting and solution.

I learned that it is not feasible to change a single cell from COMPUTE BEFORE to have 2 different background colors. PDF file shows cell with only one color. 

Could you please shed some more lights on

" However, the downside of this technique is that you won't be able to change the color and text of the "Third Line" (such as when TYPE changes) without "macro-izing" your code."

  Regards,

Girish Patel

[Cynthia_sas]

Hi:

  The fact is that making a spanning header will allow you to have a separate "cell" in which to put your "Third Line" -- but the spanning headers, like the other column header cells are fixed, they don't change. The LINE statement in your COMPUTE block can write the MAKE information, as it changes, but while "Third Line" is just text -- it will stay just text. You can't have the spanning header change with each unique make/type combination without invoking PROC REPORT for every unique combination of MAKE and TYPE. You can't write to the spanning header with a LINE statement. What is in the COLUMN statement can't be "touched" by a LINE statement.

   The fact is that if you were happy with or could live with having the Make: and Type: in one cell and did NOT need to have a different background or you could live with a different foreground color for Make and Type (totally do-able), then your current code would work. It is only that you want something that PROC REPORT and PDF will not do "out of the box" that leads to the need for a "macro-ized" solution. This is the reason that people turn to SAS Macro processing.

  Space here is too limited to go into a full example of macro-izing a solution. Do you know about SAS Macro programs and SAS Macro variables? They are frequently used to generate repeatable, re-runnable code, as described in this paper (https://support.sas.com/resources/papers/proceedings13/120-2013.pdf) which is aimed at beginners.

  But, the code below shows the use of macro variables to generate a customized PROC REPORT for 1 unique MAKE/TYPE combination (ACURA/SEDAN). There are ways with SAS macro processing to create macro variables so that in your macro program you could have a %DO loop go through the entire list and generate 1 PROC REPORT step for every unique MAKE/TYPE combination. But this program would be the core place to start.

Cynthia

** define macro variables so every invocation of PROC REPORT;

** will use these macro variables for one make/type combo that you want;

** (macro variables can be generate automatically from data);

** so this is an example of 1 make/type combo;

%let make1 = Acura;

%let type1 = Sedan;

%let mback = yellow;

%let tback = lightgreen;

ods pdf file="c:\temp\report_onemake_onetype.pdf" notoc;

ods escapechar='^';

** with macro variables, you can alter the spanning header in the COLUMN;

** statement -- there are other techniques that would allow you to;

** automatically generate the list of unique make/type combinations.;

** and using proc format, you could assign diff colors for diff make and diff type;

proc report data=sashelp.cars nowd

  style(header)={background=&tback color=black just=l}

  style(lines)={textdecoration=underline fontsize=14pt fontstyle=italic

                background=pink foreground=purple fontweight=bold};

  where make = "&make1" and type = "&type1";

  Columns ("^{style[textdecoration=underline just=l]Type: &Type1}" Make model Type Origin MSRP Invoice ) ;

  Define make / Order Noprint;

  Define model / 'Model'  style(header)={background=cxeeeeee foreground=red just=c};

  Define type / Order Noprint;

  Define origin / Order Noprint; 

  Define MSRP / sum analysis style(header)={background=cxeeeeee foreground=red just=c};

  Define invoice / sum analysis style(header)={background=cxeeeeee foreground=red just=c};

  break after type / page;

  compute before _page_ / left   style=[Font_weight=bold foreground=black background=&mback]; 

     ** colors in COMPUTE statement will override what is in PROC REPORT statement;

     line "Make: &make1"    ;

  endcomp;

run;

ods _all_ close;

title;

[GPatel]

Cynthia,

This works fine as expected. I need to modify SAS code to use Macro logic. Its doable. 

Again, thanks for your insight and optimum solution.

Regards,

Girish Patel