The SAS Output Delivery System and reporting techniques

ODS PDF COMPUTE CONDITIONAL FORMAT OVER TWO DIMENTIONAL RANGE

Reply
Contributor
Posts: 33

ODS PDF COMPUTE CONDITIONAL FORMAT OVER TWO DIMENTIONAL RANGE

[ Edited ]

Hi,  I am trying to compute a conditional highlight over a range of cells in the table. If the cells meet the benchmarch target then highlight with lime green. The target CVs are on the row "Target CV by Category".   I wouldn't know how to start.  My array at the bottom doesn't work.  Is a two dimensional array better ? It might save space. Otherwise, the computation would have to happen on column at a time. 

 

I have attached the data.   The code below works geat but has no highlights.

options missing='' nodate nonumber orientation=landscape ;
ODS LISTING close;
ods pdf file="H:\Shared\MDSEIMB\SFDS\SDS\Green Book\ARMS\Test.pdf" startpage=yes;
proc report nowindows data=Test  SPLIT='*' ls=120 
style(report)={rules=none frame=void cellspacing=0 cellpadding=3
              borderwidth=0.3 borderleftwidth=0.3 borderrightwidth=0.3 } 
style(column)={cellheight=0.175in font_face=Arial font_size=0.5 }
style(header)={font_face=Arial cellheight=0.40in font_size=0.5
               bordertopcolor=black borderbottomcolor=black 
               borderbottomwidth=0.3 backgroundcolor=white  };
title1 height=1 justify=center font=Arial " TEST RESEARCH PERFORMANCE " ;/*Commodity name*/
title2 height=1 justify=center font=Arial 4pt " I want a blank line of 4pt nothing " ;/*Commodity name*/
title3 height=1 justify=center font=Arial "TargetS and Performance by Category";

columns LeftTab COM VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8 VAR9 VAR10 VAR11 VAR12 VAR13;

define LeftTab / ' ' left style(column)={cellwidth=1.5in borderrightcolor=vigb borderrightwidth=0.3 indent=6};
define COM / style(column)={cellwidth=0.7in rightmargin=.1in borderrightwidth=1 borderrightcolor=vigb} display format=comma8.0 'COM' right ;
define VAR1 / style(column)={cellwidth=0.5in rightmargin=.1in borderrightwidth=1 borderrightcolor=vigb} display format=comma4.1 'VAR1' right;
define VAR2 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR2' right;
define VAR3 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR3' right;
define VAR4 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR4' right;
define VAR5 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR5' right;
define VAR6 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR6' right;
define VAR7 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR7' right;
define VAR8 / style(column)={cellwidth=0.5in rightmargin=.1in borderrightwidth=1 borderrightcolor=vigb} display format=comma4.1 'VAR8' right;
define VAR9 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR9' right;
define VAR10 / style(column)={cellwidth=0.5in rightmargin=.1in} display format=comma4.1 'VAR10' right;
define VAR11 / style(column)={cellwidth=0.6in rightmargin=.1in} display format=comma4.1 'VAR11' right;
define VAR12 / style(column)={cellwidth=0.5in rightmargin=.1in borderrightwidth=1 borderrightcolor=vigb} display format=comma4.1 'VAR12' right;
define VAR13 / display format=comma4.1 'VAR13' right
                  style(column)={cellwidth=0.6in  rightmargin=.1in};



compute LeftTab;
if trim(LeftTab) in ('Target U.S. CV' 'Target CV by Category') then call define
			(_row_,'style','style={background=violet bordertopcolor=black borderbottomwidth=0.3}');
if trim(LeftTab) in ('Realized U.S. CV') then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=0.3}');

if trim(LeftTab) in ('' '.' 'Realized CV by Region' 'Realized CV by Econ Class' 'Realized CV by COM Type')
then call define(_row_,'style',
			'style=[borderleftcolor=white borderrightcolor=white bordertopcolor=black borderbottomcolor=black
			bordertopwidth=0.3 borderbottomwidth=0.3 cellheight=0.38in verticalalign=bottom	font_size=.6]');

if trim(LeftTab) in ('Target U.S. CV' 'Target CV by Category') then call define(_row_,'style','style={background=lavender}');
if trim(LeftTab) in ('COM2') then call define(_row_,'style','style={borderbottomcolor=black}');

endcomp;

run;

ODS _ALL_ CLOSE;
ODS LISTING;

 

I was trying to insert the following compute statement to create the highlights, but it crashes the system.

compute dummy;

   array tars{13} tars1-tars13;

   array vals{13} VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8 VAR9 VAR10 VAR11 VAR12 VAR13;    

   do i=1 to 13;

       if trim(Left_Tab) in ('Target U.S. CV' 'Target CV by Category') then tars{i}=vals{i};

       else if vals{i} > tars{i} then call define(i+3,'style','style=[background=lime]');

   end;

endcomp;

 

sing sas 9.4

 

Ask a Question
Discussion stats
  • 0 replies
  • 193 views
  • 0 likes
  • 1 in conversation