first and last. problem

[RJY]

Hi,

I am facing a problem to assign count for repeated values. Please see below source table and output that i need. Please help.

Source data:

C_ID	CARD_Status	Date
101	ATM CARD DELIVERED	10-Oct-18
101	ATM CARD RETURNED	18-Oct-18
102	ATM CARD DELIVERED	20-Oct-18
102	ATM CARD RETURNED	29-Oct-18
102	ATM CARD DELIVERED	20-Nov-18
102	ATM CARD RETURNED	29-Nov-18
103	ATM CARD DELIVERED	10-Jan-19
103	ATM CARD RETURNED	18-Jan-19

Required Output;

C_ID	CARD_Status	Date	Attempt
101	ATM CARD DELIVERED	10-Oct-18	1
101	ATM CARD RETURNED	18-Oct-18	1
102	ATM CARD DELIVERED	20-Oct-18	1
102	ATM CARD RETURNED	10-Nov-18	1
102	ATM CARD DELIVERED	20-Nov-18	2
102	ATM CARD RETURNED	29-Nov-18	2
103	ATM CARD DELIVERED	10-Jan-19	1
103	ATM CARD RETURNED	18-Jan-19	1

for cust_id 102, ATM delivered first time and it got returned in month of OCT so attempt will 1 and second time attempt will be 2. 

I am using below code to get the output but i am not getting correct results. 

data ATM1;
set ATM;
if first.C_ID and CARD_Status = "ATM CARD DELIVERED" then cnt =1;
by C_ID ;
if CARD_Status = "ATM CARD DELIVERED" and cnt = . then cnt = 2;
if last.C_ID and dsterm = "ATM CARD RETURNED" and cnt = . then cnt = 2;
if cnt = . then cnt = 1;
run;

KR,

RJ

[Kurt_Bremser]

Try this:

data atm1;
set atm;
by c_id;
if first.c_id then attempt = 0;
if card_status = "ATM CARD DELIVERED" then attempt + 1;
run;

[novinosrin · Accepted solution]

data have;
input C_ID	CARD_Status & $20.	Date :date9.;
format date date9.;
cards;
101	ATM CARD DELIVERED	10-Oct-18
101	ATM CARD RETURNED	18-Oct-18
102	ATM CARD DELIVERED	20-Oct-18
102	ATM CARD RETURNED	29-Oct-18
102	ATM CARD DELIVERED	20-Nov-18
102	ATM CARD RETURNED	29-Nov-18
103	ATM CARD DELIVERED	10-Jan-19
103	ATM CARD RETURNED	18-Jan-19
;

data want;
set have;
by c_id;
if first.c_id then Attempt=1;
else if CARD_Status='ATM CARD DELIVERED' then Attempt+1;
run;

                                                                                       RESULTS:

C_ID	CARD_Status	Date	Attempt
101	ATM CARD DELIVERED	10OCT2018	1
101	ATM CARD RETURNED	18OCT2018	1
102	ATM CARD DELIVERED	20OCT2018	1
102	ATM CARD RETURNED	29OCT2018	1
102	ATM CARD DELIVERED	20NOV2018	2
102	ATM CARD RETURNED	29NOV2018	2
103	ATM CARD DELIVERED	10JAN2019	1
103	ATM CARD RETURNED	18JAN2019	1

[RJY]

Thanks a lot both for your quick turnaround. It solved my problem 🙂