Last period enter fault and stayed faulty since then

[Ronein]

Hello

I have a data set with information about each customer if he was in fault in each period (period 1 till 10).

value 0 means no fault

value 1 means fault.

My target- For each customer to calculate last period he/she enter to fault and stayed in fault.

For customer 111- He was never in fault  so value  should be null (calc_Last_Period_Enter_Fault=.)

For customer 222-He was always in fault (since period 1) so value should be 1 (calc_Last_Period_Enter_Fault=1)

For customer 333-He entered firstly int fault in period 7 and stayed faulty  so value should be 7(calc_Last_Period_Enter_Fault=7)

For customer 444-He entered to fault a few times but in last period he is not faulty so value should be null (calc_Last_Period_Enter_Fault=.)

For customer 555-Last period he entered fault is period 8 and since then he is faulty so value should be 8 (calc_Last_Period_Enter_Fault=8)

What is the way to calculate it  via SAS code please?

Data Have;
Input Customer_ID  period status_Fault;
cards;
111 1 0
111 2 0
111 3 0
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 0
111 10 0
222 1 1
222 2 1
222 3 1
222 4 1
222 5 1
222 6 1
222 7 1
222 8 1
222 9 1
222 10 1
333 1 0
333 2 0
333 3 0
333 4 0
333 5 0
333 6 0
333 7 1
333 8 1
333 9 1
333 10 1
444 1 0
444 2 0
444 3 1
444 4 1
444 5 1
444 6 0
444 7 0
444 8 1
444 9 1
444 10 0
555 1 0
555 2 0
555 3 1
555 4 1
555 5 0
555 6 0
555 7 0
555 8 1
555 9 1
555 10 1
;
Run;

s 

[Kurt_Bremser · Accepted solution]

data want;
set have;
by customer_id;
retain calc;
if first.customer_id or status_fault = 0 then calc = 0;
if calc = 0 and status_fault = 1 then calc = period;
if last.customer_id;
keep customer_id calc;
run;

Untested, posted from my tablet.

[Ronein]

Thanks,

How would you change the code if the request is to find last period enter fault without condition of faulty today?

In such condition customer 444 will get value 8

[Ksharp]

For you last question.

Data Have;
Input Customer_ID  period status_Fault;
cards;
111 1 0
111 2 0
111 3 0
111 4 0
111 5 0
111 6 0
111 7 0
111 8 0
111 9 0
111 10 0
222 1 1
222 2 1
222 3 1
222 4 1
222 5 1
222 6 1
222 7 1
222 8 1
222 9 1
222 10 1
333 1 0
333 2 0
333 3 0
333 4 0
333 5 0
333 6 0
333 7 1
333 8 1
333 9 1
333 10 1
444 1 0
444 2 0
444 3 1
444 4 1
444 5 1
444 6 0
444 7 0
444 8 1
444 9 1
444 10 0
555 1 0
555 2 0
555 3 1
555 4 1
555 5 0
555 6 0
555 7 0
555 8 1
555 9 1
555 10 1
;
Run;

data want;
 set have;
 by Customer_ID  status_Fault notsorted;
 retain want;
 if first.Customer_ID then call missing(want);
 if first.status_Fault and status_Fault then want=period;
 if last.Customer_ID;
run;