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
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.
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: