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
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 |
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;
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 |
Thanks a lot both for your quick turnaround. It solved my problem 🙂
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.