BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RJY
Fluorite | Level 6 RJY
Fluorite | Level 6

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_IDCARD_StatusDate
101ATM CARD DELIVERED10-Oct-18
101ATM CARD RETURNED18-Oct-18
102ATM CARD DELIVERED20-Oct-18
102ATM CARD RETURNED29-Oct-18
102ATM CARD DELIVERED20-Nov-18
102ATM CARD RETURNED29-Nov-18
103ATM CARD DELIVERED10-Jan-19
103ATM CARD RETURNED18-Jan-19

 

Required Output;

C_IDCARD_StatusDateAttempt
101ATM CARD DELIVERED10-Oct-181
101ATM CARD RETURNED18-Oct-181
102ATM CARD DELIVERED20-Oct-181
102ATM CARD RETURNED10-Nov-181
102ATM CARD DELIVERED20-Nov-182
102ATM CARD RETURNED29-Nov-182
103ATM CARD DELIVERED10-Jan-191
103ATM CARD RETURNED18-Jan-191

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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
Fluorite | Level 6 RJY
Fluorite | Level 6

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 850 views
  • 0 likes
  • 3 in conversation