topic color cells value based on variable value in ODS and Base Reporting

color cells value based on variable value

amager — Thu, 19 Sep 2019 15:35:13 GMT

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 ..

Re: color cells value based on variable value

PaigeMiller — Thu, 19 Sep 2019 15:30:05 GMT

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.

Re: color cells value based on variable value

amager — Thu, 19 Sep 2019 15:30:56 GMT

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

Re: color cells value based on variable value

Reeza — Thu, 19 Sep 2019 16:13:37 GMT

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.

Re: color cells value based on variable value

amager — Thu, 19 Sep 2019 16:30:41 GMT

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 ?!

Re: color cells value based on variable value

Reeza — Thu, 19 Sep 2019 16:32:58 GMT

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

Re: color cells value based on variable value

amager — Thu, 19 Sep 2019 16:39:03 GMT

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

Re: color cells value based on variable value

PaigeMiller — Thu, 19 Sep 2019 16:51:30 GMT

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.

Re: color cells value based on variable value

ballardw — Thu, 19 Sep 2019 16:52:09 GMT

@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.

Re: color cells value based on variable value

Cynthia_sas — Thu, 19 Sep 2019 17:20:38 GMT

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;

Re: color cells value based on variable value

amager — Thu, 19 Sep 2019 17:33:00 GMT

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

Re: color cells value based on variable value

Reeza — Thu, 19 Sep 2019 19:57:39 GMT

I still think our comments are valid. You need to make a reproducible example we can work with. If you can't from your system, use SAS OnDemand for Academics, create an example and test it there. Once you have it working, you can apply it to your actual data. Otherwise, you can consider talking to a colleague or SAS tech support directly to get help, since you should be allowed to share the data with them.

Re: color cells value based on variable value

amager — Tue, 24 Sep 2019 16:21:29 GMT

HI, Voilà my proc report , I wonder why the datflag variable can be coloried and datflagN No.. something wrong here ?!


proc report data= data_dif  nowd out=work.test02
style(header) = [ background = $col_header. font_size= 10pt ];
column STUDY NAMING_CONVENTION_CHANGE DTER	DELIVERABLE_UNIT_CODE UNIT_AMOUNT Planned_delivery_date
STUDY_MILESTONES IRIS_COMMENT CRO_COMMENTS AGREEMENT_CRO_ON_ORDER EFFECTIVE_DELIVERY_DATE	
DELIVERED_AMOUNT STATUS	IRIS_ACCEPTATION KPI1_VOLUME KPI2_DELAY	KPI3_QUALITY QUALITY_DETAILS
DATE_OF_INVOICE	STADE_PGM1_PGM2	DELETED Record_Type datflag datflagn;

define	STUDY 					 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define 	NAMING_CONVENTION_CHANGE /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define 	DTER 					 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	DELIVERABLE_UNIT_CODE 	 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	UNIT_AMOUNT				 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	Planned_delivery_date	 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	STUDY_MILESTONES		 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	IRIS_COMMENT			 /    style(header) = [ background =lightblue font_size= 10pt ]  ;	
define	CRO_COMMENTS			 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	AGREEMENT_CRO_ON_ORDER	 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	EFFECTIVE_DELIVERY_DATE	 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	DELIVERED_AMOUNT		 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	STATUS					 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	IRIS_ACCEPTATION		 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	KPI1_VOLUME				 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	KPI2_DELAY				 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	KPI3_QUALITY			 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	QUALITY_DETAILS			 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	DATE_OF_INVOICE			 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	STADE_PGM1_PGM2			 /    style(header) = [ background =lightgreen font_size= 10pt ] ;	
define	DELETED					 /    style(header) = [ background =lightblue font_size= 10pt ] ;	
define	Record_Type				 /    style(header) = [ background =red font_size= 10pt ] ;	

compute Record_Type; 
		if 	  Record_Type in  ("Manuel" "M" )   then call define(_row_,"style","style={background=Lightred}") ;
		if 	  Record_Type in  ("Automatique" "A")    then call define(_row_,"style","style={background=Lightblue}") ;
endcomp; 
 compute CRO_COMMENTS; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute AGREEMENT_CRO_ON_ORDER; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute EFFECTIVE_DELIVERY_DATE; 
 	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute DELIVERED_AMOUNT; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute STATUS; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute KPI1_VOLUME; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute KPI2_DELAY; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute DATE_OF_INVOICE; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute STADE_PGM1_PGM2; 
	call define(_COL_,"style","style={background=lightgreen}"); 
endcomp;
compute datflagn; 
	 if index(datflagn,'UNIT_AMOUNT')  then  call define(UNIT_AMOUNT,"style","style={background=red}");
	 if index(datflagn,'PLANNED_DELIVERY_DATE')  then  call define(PLANNED_DELIVERY_DATE,"style","style={background=red}"); 
endcomp;
compute datflag; 
	 if index(datflag,'DTER')  				    then  call define("DTER","style","style={background=red}"); 
	 if index(datflag,'DELIVERABLE_UNIT_CODE')  then  call define("DELIVERABLE_UNIT_CODE","style","style={background=red}"); 
	 if index(datflag,'STUDY_MILESTONES')  		then  call define("STUDY_MILESTONES","style","style={background=red}"); 
	 if index(datflag,'IRIS_COMMENTS')  		then  call define("IRIS_COMMENTS","style","style={background=red}"); 
	 if index(datflag,'CRO_COMMENTS')  			then  call define("CRO_COMMENTS","style","style={background=red}"); 
	 if index(datflag,'AGREEMENT_CRO_ON_ORDER') then  call define("AGREEMENT_CRO_ON_ORDER","style","style={background=red}"); 
	 if index(datflag,'STATUS')  				then  call define("STATUS","style","style={background=red}"); 
	 if index(datflag,'IRIS_ACCEPTATION')  		then  call define("IRIS_ACCEPTATION","style","style={background=red}"); 
endcomp;


run;

Re: color cells value based on variable value

amager — Thu, 26 Sep 2019 14:22:33 GMT

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

Re: color cells value based on variable value

amager — Thu, 26 Sep 2019 15:59:16 GMT

the entire Proc Report code in the last comments.

Re: color cells value based on variable value

Cynthia_sas — Thu, 26 Sep 2019 16:29:56 GMT

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

Re: color cells value based on variable value

amager — Fri, 27 Sep 2019 14:48:14 GMT

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,