About HR_coder123

HR_coder123 · ‎06-07-2023

I ended up taking a very similar route except using 2 unique IDs for both A and B, but essentially ended up at the same place with the data step at the end as you have proposed (using the LAST statement to get rid of the duplicates from the cartesian product). The other way I did this (very similar as well), was to just merge the A_termination dates directly onto the B records, and then use the LAST statement to get rid of the cartesian product, and finally create a variable that concatenated the employee Id and last termination date into one. Thanks again for your help everyone!

HR_coder123 · ‎06-06-2023

There will be multiple B reviews that can match with a A review. However, sometimes the B reviews map to multiple A reviews. I need them to map in a specific fashion. What I need though is for the B reviews to match with a A review where the following occurs: #1) The B reviews start and termination date are within the start and termination date of the A review. #2) The B review is paired with the A review that has the latest termination date. There can be multiple B reviews mapping to one A review. I just need to be sure that they map to the A review with the "latest" termination date. I think I have a roundabout solution to this. I will post if if I get it.

HR_coder123 · ‎06-06-2023

This is a tricky one, that I have been having issues getting the desired result. Below I have employee data where there are start dates, termination dates, 2 different review record types, and employee ids. In the data below, I need to create a unique Id to match each B review with only one A review. The matching criteria are the following: 1) The B review must fall on or between the A review dates (same employee as well). 2) When the B review matches more than one A review, then the B review must match to the A review with the latest termination date. So if it matches 2 or more A reviews, I need it to match the one with the latest termination date (see my example). This is where I'm running into problems. 3) The A reviews do not always have to match a B review. What is important is that the B reviews are correctly mapped to an A review. In my example, the unmatached A reviews have a null value o the unique Id flag. 4) A unique id flag to show the correct mapping of the B review back to the A review that fulfills criteria 1 and 2 above. data have; input employee_id rvw_type $ s_date :yymmdd10. t_Date :yymmdd10.; format s_date yymmddd10. t_date yymmdd10.; datalines; 1 A 2019-04-23 2019-04-25 1 A 2019-04-23 2019-04-27 1 A 2019-11-20 2019-11-24 1 A 2019-11-20 2019-11-26 1 B 2019-04-25 2019-04-25 1 B 2019-04-23 2019-04-23 1 B 2019-11-22 2019-11-22 2 A 2019-01-05 2019-01-06 2 A 2019-01-05 2019-01-08 2 B 2019-01-05 2019-01-05 2 B 2019-01-05 2019-01-06 ; run; data want; input _employee_id Rvw_type $ s_date :yymmdd10. t_Date :yymmdd10. Unique_match_flag; format s_date yymmddd10. t_date yymmdd10.; datalines; 1 A 2019-04-23 2019-04-25 . 1 A 2019-04-23 2019-04-27 1 1 A 2019-11-20 2019-11-24 . 1 A 2019-11-20 2019-11-26 2 1 B 2019-04-25 2019-04-25 1 1 B 2019-04-23 2019-04-23 1 1 B 2019-11-22 2019-11-22 2 2 A 2019-01-05 2019-01-06 . 2 A 2019-01-05 2019-01-08 3 2 B 2019-01-05 2019-01-05 3 2 B 2019-01-05 2019-01-06 3 ; run;

HR_coder123 · ‎05-19-2023

This is perfect, thanks so much!

HR_coder123 · ‎05-18-2023

Will this work with my follow up example, sorry I changed the original example to include an employee_num, and pull the ID? I have yet to run through the code you supplied, but it's much appreciated. data have; input employee_num id orig_id x1 $ x2 $ x3 $; datalines; 1 11 . A B C 1 22 11 D E F 1 44 22 G H I 1 57 44 J K L 1 18 57 M N O 2 88 . W T U 2 98 88 V Z Y ; RUN; DATA WANT; INPUT employee_num id Y1 $ Y2 $ Y3 $ Y4 $ Y5 $ Y6 $ Y7 $ Y8 $ Y9 $ Y10 $ Y11$ Y12 $ Y13$ Y14 $ Y15 $; datalines; 1 11 A B C D E F G H I J K L M N O 2 88 W T U V Z y . . . . . . . . . ; RUN;

HR_coder123 · ‎05-18-2023

I can elaborate more on the question by providing an employee number, the IDs represent a case num for the employee and are all distinct. data have; input employee_num id orig_id x1 $ x2 $ x3 $; datalines; 1 11 . A B C 1 22 11 D E F 1 44 22 G H I 1 57 44 J K L 1 18 57 M N O 2 88 . W T U 2 98 88 V Z Y ; RUN; DATA WANT; INPUT employee_num id Y1 $ Y2 $ Y3 $ Y4 $ Y5 $ Y6 $ Y7 $ Y8 $ Y9 $ Y10 $ Y11$ Y12 $ Y13$ Y14 $ Y15 $; datalines; 1 11 A B C D E F G H I J K L M N O 2 88 W T U V Z y . . . . . . . . . ; RUN;

HR_coder123 · ‎05-17-2023

I got the following HR data set with recursive IDs that all link back to an original ID. However, there are character variables that I also need to bring along with the recursive data set. In the example below, the initial ID is 11, and the follow on ID 22 links via an originating ID 11 and so on. As you can see, they all link back to 11 via a recursive lookup. I've used the chained lookup code here https://support.sas.com/kb/26/160.html, but can't get the variables to come along. data have; input id orig_id x1 $ x2 $ x3 $; datalines; 11 . A B C 22 11 D E F 44 22 G H I 57 44 J K L 18 57 M N O ; DATA WANT; INPUT id Y1 $ Y2 $ Y3 $ Y4 $ Y5 $ Y6 $ Y7 $ Y8 $ Y9 $ Y10 $ Y11$ Y12 $ Y13$ Y14 $ Y15 $; datalines; 11 A B C D E F G H I J K L M N O ; RUN;

Online Status	Offline
Date Last Visited	‎06-29-2023 02:29 PM

Re: Create a unique ID for overlapping records with a twist

Re: Create a unique ID for overlapping records with a twist

Create a unique ID for overlapping records with a twist

Re: Recursive or chained lookup with a data twist

Re: Recursive or chained lookup with a data twist

Re: Recursive or chained lookup with a data twist

Recursive or chained lookup with a data twist

Re: Create a unique ID for overlapping records with a twist

Re: Create a unique ID for overlapping records with a twist

Create a unique ID for overlapping records with a twist

Re: Recursive or chained lookup with a data twist

Re: Recursive or chained lookup with a data twist

Re: Recursive or chained lookup with a data twist

Recursive or chained lookup with a data twist