topic ODS Excel - Conditional formatting - Consider a variable empty while populated in SAS Programming

ODS Excel - Conditional formatting - Consider a variable empty while populated

Roger5 — Tue, 17 Mar 2020 18:13:20 GMT

Hi,

I am trying to apply condition formatting to a variable based on another numeric variable.

The variable "num" is always populated.

Unfortunately, without any error message or warning, the conditional formatting doesn't work.

When num=9999 none of the lines are italicized.

compute var;

if num =9999 then call define(_COL_,'style','style={font_style=italic}');

endcomp;

When num ne 9999 all lines are italicized.

compute var;

if num ne 9999 then call define(_COL_,'style','style={font_style=italic}');

endcomp;

When missing(num)=1 all lines are italicized.

compute var;

if missing(num)=1 then call define(_COL_,'style','style={font_style=italic}');

endcomp;

However, when do the following test on the dataset used in the proc report the variable num seems fine:

data dataset_test;

set dataset;

if num=9999;

run;

NOTE: There were 34 observations read from the data set WORK.dataset.

NOTE: The data set WORK.dataset_TEST has 24 observations and 25 variables.

Also note that I have the following lines prior to the conditional formatting.

columns var num;

define num / order noprint;

Any Idea why the variable num is not correctly assessed?

Thanks!

Re: ODS Excel - Conditional formatting - Consider a variable empty while populated

Tom — Tue, 17 Mar 2020 18:36:18 GMT

It is not clear what code you actually run.

But it looks like you might be trying to reference a variable BEFORE it exists in the report.

If VAR is before NUM in the report then in the compute block for VAR the value of NUM is not known yet.

Re: ODS Excel - Conditional formatting - Consider a variable empty while populated

Roger5 — Tue, 17 Mar 2020 18:48:26 GMT

Here's my code:

proc report data=dataset nofs nowd headline contents='' spanrows

style(header)=[foreground=black background=white font_weight=bold /*background=#D3D3D3*/ font_size=9.5pt cellheight=0.25in fontfamily=arial vjust=m bordercolor=black] split='/'
style(report)=[foreground=black background=white font_size=9.5pt cellpadding =4.pt];

columns num var ;

       define var/ order display "variable"
        style(header)=[just=L background=white bordercolor=black foreground=black]
        style(column)=[just=L bordercolor=black font_size=9.5pt fontfamily=arial width=6.0in ] ;

      define num/ order noprint;

  compute var;
   if missing(num)=1 then call define(_COL_,'style','style={font_style=italic}');
  endcomp;

run;