First let's convert your listings into actual SAS datasets. Let's call them TRANSACTIONS and CARDS.
data transactions;
input ID_NUM $ EMP_NM $ DATE :mmddyy. (CALL_START CALL_END) (:time.) CALL_DUR ;
format date yymmdd10. call_start call_end tod5. ;
cards;
000121 Mark 6/4/2023 4:30PM 4:32PM 2
000121 Mark 8/15/2023 8:30AM 8:31AM 1
000121 Mark 9/6/2023 9:15AM 9:18AM 3
000121 Mark 5/6/2024 10:05AM 10:06AM 1
000121 Mark 6/10/2024 2:22PM 2:23PM 1
000122 Jessie 4/29/2023 8:45AM 8:55AM 10
000122 Jessie 11/29/2023 11:15AM 11:30AM 15
000122 Jessie 12/8/2023 1:23PM 1:40PM 17
000122 Jessie 2/16/2024 1:40PM 1:55PM 15
000123 James 2/8/2023 12:10PM 12:20PM 10
000123 James 2/22/2023 2:33PM 2:55PM 22
000123 James 1/21/2024 4:12PM 4:19PM 7
000124 Felicia 5/23/2024 3:42PM 3:55PM 13
000127 Henry 1/2/2023 9:02AM 9:10AM 8
000127 Henry 3/18/2023 9:50AM 10:00AM 10
000127 Henry 4/21/2023 10:05AM 10:20AM 15
000127 Henry 10/26/2023 1:22PM 1:27PM 5
000127 Henry 11/12/2023 2:00PM 2:04PM 4
;
data cards;
infile cards truncover;
input ID_NUM $ EMP_NM $ (EFF_DT END_DT) (:mmddyy.) CARD $ CARD_DESC $50.;
format EFF_DT END_DT yymmdd10.;
cards;
000121 Mark 1/1/2020 12/31/2020 GREEN Complete calls within 15 minutes.
000121 Mark 1/1/2021 12/31/2021 BLUE Complete calls within 5 minutes.
000121 Mark 1/1/2022 12/31/2022 ORANGE Complete calls within 8 minutes.
000121 Mark 1/1/2023 12/31/2023 GREEN Complete calls within 15 minutes.
000121 Mark 1/1/2024 12/31/2024 BLUE Complete calls within 5 minutes.
000122 Jessie 1/1/2022 12/31/2022 GREEN Complete calls within 15 minutes.
000122 Jessie 1/1/2023 12/31/2023 GREEN Complete calls within 15 minutes.
000122 Jessie 1/1/2024 12/31/2024 GREEN Complete calls within 15 minutes.
000123 James 7/1/2022 6/30/2023 GREEN Complete calls within 15 minutes.
000123 James 7/1/2023 6/30/2024 ORANGE Complete calls within 8 minutes.
000123 James 7/1/2024 6/30/2025 YELLOW Complete calls within 10 minutes.
000124 Felicia 3/1/2024 2/28/2025 GREEN Complete calls within 15 minutes.
000127 Henry 1/1/2023 12/31/2023 GREEN Complete calls within 15 minutes.
000127 Henry 1/1/2024 12/31/2024 BLUE Complete calls within 5 minutes.
;
It is much easier to do matches on ranges with SQL syntax, so let's do a left join of TRANSACTIONS with CARDS so we have all of the information on the same observations. We can then extract the target duration from the descriptive text and convert to a number that we can compare to the call duration.
proc sql;
create table want as
select a.*
, b.card
, b.card_desc
, input(scan(b.card_desc,-2,' '),32.) as target
, (a.CALL_DUR between 0 and calculated target) as satisfied
from transactions a
left join cards b
on a.ID_NUM = b.ID_NUM
and a.DATE between b.EFF_DT and b.END_DT
order by a.ID_NUM, a.date, a.call_start
;
quit;
Results
... View more
