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