Find last month customer enter failure in Long structure data

Ronein — Tue, 14 Jan 2025 06:47:14 GMT

Hello

For each customer I have 12 months follow up with indicator of failure (1/0).

I want to know when is the last month (closest month to today: today is month index 12) that customer enter to failue.

For example:

Customer 111: month 9

Customer 222: month 11

Customer 333: Null Since today not in failure).

Customer 444: month 1 since he was always in failure)

Pay attention that if customer not in failure today then get value null.

What is the way to calculate it please?


Data have;
input CustID monindex failureInd;
cards;
111 1 0
111 2 0
111 3 1 
111 4 1
111 5 0
111 6 0
111 7 1 
111 8 0
111 9 1
111 10 1
111 11 1
111 12 1
222 1 0
222 2 0
222 3 0 
222 4 0
222 5 0
222 6 0
222 7 0 
222 8 0
222 9 0
222 10 0
222 11 1
222 12 1
333 1 0
333 2 0
333 3 0 
333 4 0
333 5 0
333 6 0
333 7 0 
333 8 0
333 9 1
333 10 1
333 11 0
333 12 0
444 1 1
444 2 1
444 3 1 
444 4 1
444 5 1
444 6 1
444 7 1 
444 8 1
444 9 1
444 10 1
444 11 1
444 12 1
;
Run;

Re: Find last month customer enter failure in Long structure data

Ksharp — Tue, 14 Jan 2025 07:25:45 GMT

Data have;
input CustID monindex failureInd;
cards;
111 1 0
111 2 0
111 3 1 
111 4 1
111 5 0
111 6 0
111 7 1 
111 8 0
111 9 1
111 10 1
111 11 1
111 12 1
222 1 0
222 2 0
222 3 0 
222 4 0
222 5 0
222 6 0
222 7 0 
222 8 0
222 9 0
222 10 0
222 11 1
222 12 1
333 1 0
333 2 0
333 3 0 
333 4 0
333 5 0
333 6 0
333 7 0 
333 8 0
333 9 1
333 10 1
333 11 0
333 12 0
444 1 1
444 2 1
444 3 1 
444 4 1
444 5 1
444 6 1
444 7 1 
444 8 1
444 9 1
444 10 1
444 11 1
444 12 1
;
Run;


data temp;
 set have;
 by CustID failureInd notsorted;
 if first.failureInd ;
run;

data want;
 set temp;
 by CustID;
 if failureInd=0 then call missing(monindex);
 if last.CustID;
 drop failureInd;
run;

topic Find last month customer enter failure in Long structure data in SAS Programming

Find last month customer enter failure in Long structure data

Re: Find last month customer enter failure in Long structure data