Lag with calculate indicator

Ronein — Wed, 18 Sep 2024 09:42:59 GMT

Hello

For each customer I have 12 rows which represent data of last 12 months.

Month 1 is last month.

Month 12 is the month 12 months ago.

For each customer I want to calculate for each row (From row 2 till row 12) an indicator that get value 1 IF at least one of following conditions happen:

condition1- If from month to next month there is increase of more than 10% and the next value ratio is higher than 0.5

condition2-IF from one month to next month there is increase and previous month is lower than 1 and next month is higher than 1

for example:

month1= 0.4 , month2= 0.45 then indicator=0 because both conditions no meet

month1=0.4 month2=0.6 then indicator=1 because condition 1 meet

month1=0.98 month2=1.01 then indicator=1 because condition 2 meet

month1=1.1 month2=1.3 then indicator=1 because condition 1 meet

what is the way to calculate the indicator please?

Data have;
input CustID month Ratio;
cards;
1 1 0.4
1 2 0.45
1 3 0.2
1 4 0.7
1 5 0.75
1 6 0.5
1 7 0.7
1 8 0.9
1 9 1.2
1 10 1.4
1 11 0.8
1 12 0.7
2 1 0.3
2 2 0.25
2 3 0.4
2 4 0.9
2 5 0.85
2 6 0.6
2 7 0.5
2 8 0.8
2 9 1.3
2 10 0.7
2 11 0.7
2 12 0.4
;
Run;

Re: Lag with calculate indicator

PeterClemmensen — Wed, 18 Sep 2024 10:53:11 GMT

Data have;
input CustID month Ratio;
cards;
1 1 0.4
1 2 0.45
1 3 0.2
1 4 0.7
1 5 0.75
1 6 0.5
1 7 0.7
1 8 0.9
1 9 1.2
1 10 1.4
1 11 0.8
1 12 0.7
2 1 0.3
2 2 0.25
2 3 0.4
2 4 0.9
2 5 0.85
2 6 0.6
2 7 0.5
2 8 0.8
2 9 1.3
2 10 0.7
2 11 0.7
2 12 0.4
;
Run;


data want;

   merge have 
         have(firstobs = 2 rename = (Ratio = lead_r));
   by CustID;
   
   lag_r = ifn(first.CustID, ., lag(Ratio));
   if last.CustID then lead_r = .;
   
   /* Condition 1 */ 
   indicator = (Ratio > 1.1*lag_r and lead_r > .5);
  
   /* Condition 2 */ 
   indicator = max(indicator, (Ratio > lag_r and lag_r < 1 and lead_r > 1)); 
   
   drop lead_r lag_r;
   
run;

topic Lag with calculate indicator in SAS Programming

Lag with calculate indicator

Re: Lag with calculate indicator