topic Re: Last period enter fault and stayed faulty since then in SAS Programming

Last period enter fault and stayed faulty since then

Ronein — Sat, 16 Dec 2023 06:45:25 GMT

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;

Re: Last period enter fault and stayed faulty since then

Kurt_Bremser — Sat, 16 Dec 2023 06:53:47 GMT

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.

Re: Last period enter fault and stayed faulty since then

Ronein — Sat, 16 Dec 2023 07:32:59 GMT

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

Re: Last period enter fault and stayed faulty since then

Ksharp — Sat, 16 Dec 2023 09:46:12 GMT

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;