Proc Report-Color rows If there is change in value between specific columns

[Ronein]

Hello

I have data set in Long structure.

This data set included information two groups of columns:

1-Columns with general information about customer (These rows are with value Var_groups=1)

2- Columns with  information of categorical explanatory variables  (These rows are with value Var_groups=2)

My task:

Create printed report with same structure (Long)

that will color rows that their values in columns mon1,mon2,mon3,mon4,mon5,mon6  are not equal each other 

(IF there is one column that differ from other than need to color all row in yellow)

I want to apply this rule only on rows with value Var_groups=2

Data summary_Report_Long_Strcuture;
infile datalines delimiter=',';
Input  Var_name $  Var_groups $ Mon1 $ Mon2  $ Mon3 $ Mon4  $ Mon5 $ Mon6 $;
cards;
month,1,2210,2211,2212,2301,2302,2303
customer,1,777,777,777,777,777,777
branch,1,23,23,23,23,23,23
X1,2,a,a,a,a,a,a 
W1,2,1,1,1,2,1,1 
Z1,2,1,1,1,1,1,1 
X1,2,1,2,1,1,1,1 
;
Run;

[Cynthia_sas]

Hi:
What code have you tried? This is going to need a COMPUTE block and you're either going to need to pre-process your data to create a flag variable that can be used to determine whether the row should be highlighted. If you don't pre-process the data ahead of time, then you'll need to do your comparison of all the variables in the report row on the last variable you display before you'd have enough information to highlight the row. Remember the left-to-right rule of PROC REPORT. Until PROC REPORT has placed ALL the MONx variables on the report row, you can't test them ALL against each other.
Cynthia

[Ronein · Accepted solution]

Thanks,

I created this code but I cannot see any line with color in the output report.

Data summary_Report_Long_Strcuture;
infile datalines delimiter=',';
Input  Var_name $  Var_groups $ Mon1 $ Mon2  $ Mon3 $ Mon4  $ Mon5 $ Mon6 $;
cards;
month,1,2210,2211,2212,2301,2302,2303
customer,1,777,777,777,777,777,777
branch,1,23,23,23,23,23,23
X1,2,a,a,a,a,a,a 
W1,2,1,1,1,2,1,1 
Z1,2,1,1,1,1,1,1 
X1,2,1,2,1,1,1,1 
;
Run;

 data want;
 set summary_Report_Long_Strcuture;
 IF Var_groups=2 and ((Mon2 ne Mon1) OR (Mon3 ne Mon2)OR (Mon4 ne Mon3)OR (Mon5 ne Mon4)OR (Mon6 ne Mon5))  then Ind_Change=1;else Ind_Change=0;
 Run;


proc report data=want nowd ;
column Var_name Var_groups Mon1 Mon2 Mon3 Mon4 Mon5 Mon6 Ind_Change;
define Var_name   /display;
define Var_groups   /display;
define Mon1   /display;
define Mon2   /display;
define Mon3   /display;
define Mon4   /display;
define Mon5   /display;
define Mon6   /display;
compute Ind_Change;
if Ind_Change=1 then
call define(_row_, "style", "style=[backgroundcolor=yellow]");
endcomp;
run;

[Ronein]

Why did my code didnt color the relevant rows please?

[PaigeMiller]

You don't have a DEFINE statement for IND_CHANGE.

Add this into your code:

define Ind_Change/noprint display;

Also, you can use simple math to see if the values in MON1-MON6 are equal or not. In a data step use

if Var_groups=2 and range(of mon:)>0 then Ind_Change=1;
else Ind_Change=0;

This data is not in "long structure", it is in wide structure.

[Ksharp]

Data summary_Report_Long_Strcuture;
infile datalines delimiter=',';
Input  Var_name $  Var_groups $ Mon1 $ Mon2  $ Mon3 $ Mon4  $ Mon5 $ Mon6 $;
cards;
month,1,2210,2211,2212,2301,2302,2303
customer,1,777,777,777,777,777,777
branch,1,23,23,23,23,23,23
X1,2,a,a,a,a,a,a 
W1,2,1,1,1,2,1,1 
Z1,2,1,1,1,1,1,1 
X1,2,1,2,1,1,1,1 
;
Run;
proc report data=summary_Report_Long_Strcuture nowd;
compute Mon6;
array x{*} $ Mon1-Mon6;
if Var_groups='2' then do;
 do i=2 to dim(x);
 if x{1} ne x{i} then do;
   call define(_row_,'style','style={background=gold}');
   leave;
 end;
 end;
end;
endcomp;
run;