Update value within a date range from multiple data sets

[itsalexis]

Hi,

I have been searching for a solution to this for a while and my googling hasn't resulted in anything tangible. I hope someone here can help.

I have 2 datasets (all data is made up but the gist should be there):

1. Transaction_Tbl - the source dataset that contains transactional data of call reps for selected dates and call durations:

Transaction_Tbl
ID_NUM	EMP_NM	DATE	CALL_START	CALL_END	CALL_DUR
000121	Mark	6/4/2023	4:30 PM	4:32 PM	2
000121	Mark	8/15/2023	8:30 AM	8:31 AM	1
000121	Mark	9/6/2023	9:15 AM	9:18 AM	3
000121	Mark	5/6/2024	10:05 AM	10:06 AM	1
000121	Mark	6/10/2024	2:22 PM	2:23 PM	1
000122	Jessie	4/29/2023	8:45 AM	8:55 AM	10
000122	Jessie	11/29/2023	11:15 AM	11:30 AM	15
000122	Jessie	12/8/2023	1:23 PM	1:40 PM	17
000122	Jessie	2/16/2024	1:40 PM	1:55 PM	15
000123	James	2/8/2023	12:10 PM	12:20 PM	10
000123	James	2/22/2023	2:33 PM	2:55 PM	22
000123	James	1/21/2024	4:12 PM	4:19 PM	7
000124	Felicia	5/23/2024	3:42 PM	3:55 PM	13
000127	Henry	1/2/2023	9:02 AM	9:10 AM	8
000127	Henry	3/18/2023	9:50 AM	10:00 AM	10
000127	Henry	4/21/2023	10:05 AM	10:20 AM	15
000127	Henry	10/26/2023	1:22 PM	1:27 PM	5
000127	Henry	11/12/2023	2:00 PM	2:04 PM	4

2. Card_Tbl - The supplemental dataset that shows what "card" the rep held between a certain date range. The card changes annually based on performance and defines the expected call duration at the card level:

Card_Tbl
ID_NUM	EMP_NM	EFF_DT	END_DT	CARD	CARD_DESC
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.

I am currently reviewing the rep call duration manually by dumping the data to excel and adding the relevant card color from Card_tbl and determining a pass or fail if the call duration meets expectation.

The table i'd like to get by using SAS instead of manual work in excel is this (and complete a full population review vs a manual one):

ID_NUM, EMP_NM, DATE, and CALL_DUR from Transaction_Tbl

CARD from Card_Tbl based on the date

RESULT is new to the want_tbl and will show pass/fail depending on CALL_DUR and CARD value

Want_tbl
ID_NUM	EMP_NM	DATE	CALL_DUR	CARD	RESULT
000121	Mark	6/4/2023	2	GREEN	PASS
000121	Mark	8/15/2023	1	GREEN	PASS
000121	Mark	9/6/2023	3	GREEN	PASS
000121	Mark	5/6/2024	1	BLUE	PASS
000121	Mark	6/10/2024	1	BLUE	PASS
000122	Jessie	4/29/2023	10	GREEN	PASS
000122	Jessie	11/29/2023	15	GREEN	PASS
000122	Jessie	12/8/2023	17	GREEN	FAIL
000122	Jessie	2/16/2024	15	GREEN	PASS
000123	James	2/8/2023	10	GREEN	PASS
000123	James	2/22/2023	22	GREEN	FAIL
000123	James	1/21/2024	7	ORANGE	PASS
000124	Felicia	5/23/2024	13	GREEN	PASS
000127	Henry	1/2/2023	8	GREEN	PASS
000127	Henry	3/18/2023	10	GREEN	PASS
000127	Henry	4/21/2023	15	GREEN	PASS
000127	Henry	10/26/2023	5	GREEN	PASS
000127	Henry	11/12/2023	4	GREEN	PASS

I cannot figure out how to grab the applicable CARD value from the Card_Tbl as it applies to the ID_NUM/date in the Transaction_Tbl.

Thank You for your time and expertise.

[Kurt_Bremser]

proc sql;
create table want as
  select
    t1.id_num,
    t1.emp_nm,
    t1.date,
    t1.call_dur,
    t2.card
  from trans t1 left join card t2
  on t1.id_num = t2.id_num and t2.eff_dt le t1.date le t2.end_dt
;
quit;

You can then code the condition in a DATA step, or you can integrate it in the SQL if you're versed with SQL CASE.

[Tom · Accepted solution]

First let's convert your listings into actual SAS datasets.  Let's call them TRANSACTIONS and CARDS.

Spoiler

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