topic Re: Proc Report-Color rows If there is change in value between specific columns in SAS Programming

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

Ronein — Sun, 14 May 2023 21:56:25 GMT

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;

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

Cynthia_sas — Mon, 15 May 2023 01:07:32 GMT

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

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

Ronein — Mon, 15 May 2023 05:18:54 GMT

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;

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

Ronein — Mon, 15 May 2023 08:51:05 GMT

Why did my code didnt color the relevant rows please?

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

PaigeMiller — Mon, 15 May 2023 10:27:26 GMT

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.

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

Ksharp — Mon, 15 May 2023 11:40:09 GMT

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;