color cells value based on variable value

[amager]

Hi, 

i have done a proc compare bettween 2 datasets.  i select variables of values that have changed. 

my aim is to highlights those changed values. 

in my compare datasets i have a new variable : "datflag" that contains the variables that had been modified for each row. 

Now with proc report i want to color those values cells. 

My idea is to search in this variable datflag and color the variable cells : 

My dataset like : E.g if "VAR22" in "DATFLAG" then color the 4th cells of variable "VAR22"

I want to do it with an array in proc compute that, loop for each variable . but array didn't work . 

Any Suggestions ..

[PaigeMiller]

Please show us the entire LOG (not just the errors or warnings) for this PROC, as text, not as an image. Please preserve the formatting and make it readable by clicking on the {i} icon and pasting the text of the log into that window. Please do not skip this step.

Also, show us a portion of your data.

Note for the future, when you say "it didn't work", that's never enough information. We usually will need to see the code/log and probably a portion of your data.

[amager]

Hi @Paige , "didin't work" means no error no warning but no highlighting color in output html. Sorry i can't give any portion of data because it's private. i'm answering for any explantions

[Reeza]

We don't need real data. Show your code and if you can't share data test it it on SASHELP.CLASS and show that code. It's also a good way to debug - when doing this I often find the answer before posting my questions.

[amager]

I found why index parameters was switched. now i can color the cells of the variable if it found in "datflag". BUT, must set all colonne in the compute of dataflag variable. if anyone have a best solution ??

But, i have an error with the numeric variable "Unit_amount " . 

ERROR : invalid column specification in call define. 

i check in column statement, the variable 'unit_amount' is declared. if set without quote can pass with any error, E.G : 

call define( Unit_amount,'style', ...)

Any help ?!

[Reeza]

Is it an ACROSS variable?
Where is it located in the COLUMN list? That can affect the usage.

[amager]

is not an ACROSS variable, indeed,is declared both in column and define statements.

[PaigeMiller]

I repeat that we need to see the log for the ENTIRE PROC, not just the part where you think the error is. And we would like it as text, please, and not a screen capture.

[ballardw]

---

@amager wrote:
is not an ACROSS variable, indeed,is declared both in column and define statements.

---

And now you know one reason you were asked to post the entire code. We are having to pull details, such as column position, one item at a time because you did not post the actual entire code.

Proc Report is very order dependent. The procedure builds the actual report from left to right using the column order. Any thing done in a variable call define block that references a variable to the right of that column does not "work" as that value is not available.

Also the roles of specific variables can limit what use may be made. So

Post the entire Proc Report code.

[amager]

the entire Proc Report code in the last comments.

[Cynthia_sas]

Hi:
Yes, AND as @Reeza has indicated, the order of the variables in the COLUMN statement and the usage of the variable (DISPLAY, ORDER, GROUP, SUM, etc) will both impact how you do the coding in your COMPUTE block.

Please search in the forum for previous postings on "left-to-right" rule or "left to right" rule. There have been several postings that show how the COLUMN statement order impacts your COMPUTE, like this one https://communities.sas.com/t5/SAS-Programming/PROC-REPORT-COMPUTE/td-p/477670
Cynthia

Here's a program that illustrates how COLUMN order makes a difference:

title '1) Example left-to-right rule NOT work for Alfred';
title2 'because of left to right rule about COLUMN statement';
proc report data=sashelp.class;
  column age sex name height weight ;
  define age / display;
  define sex / display;
  define name / order;
  define height / display;
  define weight / display;
  compute age;
    if name = 'Alfred' then
	   call define(_col_,'style','style={background=lightyellow}');
  endcomp;
  compute weight;
    if name in ('Alice' 'Henry') then
	   call define(_col_,'style','style={background=lightblue}');
  endcomp;
run;
  
title '2) Example left-to-right rule NOW works for all';
proc report data=sashelp.class;
  column age sex name height weight ;
  define age / display;
  define sex / display;
  define name / order;
  define height / display;
  define weight / display;
  compute weight;
    if name in ('Alice' 'Henry') then
	   call define(_col_,'style','style={background=lightblue}');
    else if name = 'Alfred' then
	   call define('name','style','style={background=lightyellow}');
  endcomp;
run;

  
title '3) Example left-to-right rule with dummy var';
title2 'Variable at end of COLUMN statement can "touch" all other items on the row';
proc report data=sashelp.class;
  column age sex name height weight dummy;
  define age / display;
  define sex / display;
  define name / order;
  define height / display;
  define weight / display;
  define dummy / computed noprint;
  compute dummy;
    dummy=1;
    if name = 'Barbara' then do;
	   call define('name','style','style={background=lightgreen}');
	   call define('height','style','style={background=lightred}');
	   call define('weight','style','style={background=lightblue}');
    end;
    else if name = 'Alfred' then
	   call define('name','style','style={background=lightyellow}');
    else if name in ('Alice' 'Henry') then
	   call define('weight','style','style={background=lightblue}');
  endcomp;
run;

[amager]

Hi @Cynthia_sas, Can help me in this report with my code below. why numeric variable didn't display but the character variables can ? .

[Cynthia_sas · Accepted solution]

Hi:

  Sorry -- there's no clue in your code which variables are numeric and which are character. Usually, I can tell based on the usage, but your DEFINE statements don't have a usage, which means that the default usage is involved -- DISPLAY for character and ANALYSIS for numeric (with a default analysis statistic of sum).

  In addition to the left-to-right rule of PROC REPORT, you also have to be aware of the naming conventions for PROC REPORT. GROUP, ORDER or DISPLAY usage items can be referenced in a COMPUTE block by their item names. So for example, in SASHELP.CLASS, the variables NAME and SEX, both character variables would have a default usage of DISPLAY, which means I can refer to them as just NAME and SEX in an IF statement or a CALL DEFINE.

  But, AGE, HEIGHT and WEIGHT are numeric variables. If I don't have a usage of DISPLAY or ORDER or GROUP for them in a DEFINE statement, I have to reference them by their "compound" names: AGE.SUM, HEIGHT.SUM and WEIGHT.SUM in an IF statement or a CALL DEFINE.

  I don't see any usages in your code -- nothing is defined to be either DISPLAY or ORDER or GROUP or ANALYSIS. So that means all the default usages are being used by PROC REPORT. Since your code has only a few clues about which items are numeric and which are character, it's hard to say why you code isn't working.

My guess is that these variables are character: datflagn, datflag, Record_Type. I would expect issues with these COMPUTE blocks

The DATFLAGN COMPUTE block will have issues because the 1st argument to CALL DEFINE must be quoted as a variable name if you are not using _COL_ or _ROW_. The DATFLAG COMPUTE block is probably working OK for character variables because you've quoted the variable names in the CALL DEFINE statement. If any of those variables in the COMPUTE blocks are numeric variables, then whether the CALL DEFINE will work ultimately depends on whether you have a usage of DISPLAY or not for the numeric variables. I don't see any usages in your DEFINE statements, so any numeric variables probably are not going to work because numeric variables have a default usage of analysis with the default statistic of SUM. You either need a usage of DISPLAY or you need to use the compound name in your COMPUTE block as shown below.

  So, as an example in SASHELP.CLASS if I have THIS for the AGE variable:

define age / style(header)={background=lightyellow};

Without a default usage my COMPUTE block would need an IF statement like this:

if age.sum ge 14 ... or

my CALL DEFINE statement would need to be:

call define('age.sum','style','...');

  But, if I have a usage of DISPLAY for the AGE variable:

define age / display style(header)={background=lightyellow};

Then with a usage of DISPLAY, my COMPUTE block would need an IF statement like this:

if age ge 14 ... or

my CALL DEFINE statement would need to be:

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

But without data to test with, and without usages in your code, it's really hard to make more than general suggestions.

  Try this code and note that #1 does NOT work correctly for the WEIGHT compute block but does work for the HEIGHT compute block. In Example #2, the CALL DEFINE works for both HEIGHT and WEIGHT.

proc report data=sashelp.class;
  title '1) Code for WEIGHT numeric variable NOT work but code for HEIGHT is OK';
  column name age sex height weight;
  define name / style(header)={background=peachpuff};
  define age / style(header)={background=lightyellow};
  define sex / style(header)={background=peachpuff};
  define height / style(header)={background=lightyellow};
  define weight / style(header)={background=lightyellow};
  compute height;
     if age.sum ge 13 and height.sum gt 65 and name ne 'Alfred' then do;
	   call define(_col_,'style','style={font_weight=bold color=green font_size=14pt');
	   call define('name','style','style={font_weight=bold color=green font_size=14pt');
     end;
	 else if name = 'Alfred' then do;
	   call define('name','style','style={font_weight=bold color=red font_size=14pt');
	   call define(_col_,'style','style={font_weight=bold color=red font_size=14pt');
	 end;
  endcomp;
  compute weight;
    if age lt 15 and weight gt 100 then do;
	   call define(_col_,'style','style={font_weight=bold color=purple font_size=14pt');
	   call define('sex','style','style={font_weight=bold color=purple font_size=14pt');
	   call define('name','style','style={font_weight=bold color=purple font_size=14pt');
     end;
  endcomp;
run;

proc report data=sashelp.class;
  title '2) Use compound name for numeric variables with default usage';
  column name age sex height weight;
  define name / style(header)={background=peachpuff};
  define age / style(header)={background=lightyellow};
  define sex / style(header)={background=peachpuff};
  define height / style(header)={background=lightyellow};
  define weight / style(header)={background=lightyellow};
  compute height;
     if age.sum ge 13 and height.sum gt 65 and name ne 'Alfred' then do;
	   call define(_col_,'style','style={font_weight=bold color=green font_size=14pt');
	   call define('name','style','style={font_weight=bold color=green font_size=14pt');
     end;
	 else if name = 'Alfred' then do;
	   call define('name','style','style={font_weight=bold color=red font_size=14pt');
	   call define(_col_,'style','style={font_weight=bold color=red font_size=14pt');
	 end;
  endcomp;
  compute weight;
    if age.sum lt 15 and weight.sum gt 100 then do;
	   call define(_col_,'style','style={font_weight=bold color=purple font_size=14pt');
	   call define('sex','style','style={font_weight=bold color=purple font_size=14pt');
	   call define('name','style','style={font_weight=bold color=purple font_size=14pt');
     end;
  endcomp;
run;

Hope this helps,

Cynthia

[amager]

this the answer for my question :
"If any of those variables in the COMPUTE blocks are numeric variables, then whether the CALL DEFINE will work ultimately depends on whether you have a usage of DISPLAY or not for the numeric variables."
THANK YOU @ Cynthia_sas,

[amager]

Paige, i would to but i work from a virtual machine copy-paste didn't work ,,sorry.