That is right @ChrisNZ . My mistake. I did not explain Date_A and Date_B match and I did not explain the cycle column clearly in my original post.
Here is another approach, using a double merge if you'd like to try playing with the code. It's a larger number of steps, but each step is fairly simple, and we may get better results this way.
I'm completely confused as to what exactly the requirements are for this code, so I will turn the code over to you, and you should then adjust the code as necessary.
Jim
Data A;
INPUT ID_A Date_A Cycle_A;
Cycle_A_1 = Cycle_A - 1;
DATALINES;
1 2015 2016
1 2016 2016
1 2017 2018
1 2018 2018
2 2015 2016
2 2016 2016
2 2017 2018
3 2013 2014
;
RUN;
**------------------------------------------------------------------------------**;
PROC SORT DATA=A
Out =A1;
BY ID_A Cycle_A Date_A;
RUN;
**------------------------------------------------------------------------------**;
PROC SORT DATA=A
Out =A2;
BY ID_A Cycle_A_1 Date_A;
RUN;
**------------------------------------------------------------------------------**;
Data B;
INPUT ID_B Date_B;
DATALINES;
1 2015
1 2016
1 2017
2 2016
2 2017
3 2018
;
RUN;
**------------------------------------------------------------------------------**;
PROC SORT DATA=B;
BY ID_B Date_B;
RUN;
**------------------------------------------------------------------------------**;
DATA A1_B;
DROP _:;
FORMAT ID Date_A Cycle_A Date_B Cycle_A_1;
LENGTH Match $10;
MERGE A1 (IN=A RENAME=(ID_A=ID Cycle_A=Date_B))
B (IN=B RENAME=(ID_B=ID));
BY ID Date_B;
IF A AND B;
Cycle_A = Date_B;
Match = 'A B Direct';
RUN;
**------------------------------------------------------------------------------**;
DATA A2_B;
DROP _:;
FORMAT ID Date_A Cycle_A Date_B Cycle_A_1;
LENGTH Match $10;
MERGE A2 (IN=A RENAME=(ID_A=ID Cycle_A_1=Date_B))
B (IN=B RENAME=(ID_B=ID));
BY ID Date_B;
IF A AND B;
Cycle_A_1 = Date_B;
Match = 'A-1 B';
RUN;
**------------------------------------------------------------------------------**;
PROC SQL NOPRINT;
CREATE TABLE A_B_Combined AS
SELECT * FROM A1_B
UNION
SELECT * FROM A2_B
;
QUIT;
**------------------------------------------------------------------------------**;
** Pre-Sort. **;
**------------------------------------------------------------------------------**;
PROC SORT DATA=A_B_Combined;
BY _ALL_;
RUN;
**------------------------------------------------------------------------------**;
** Sort de-dup. **;
**------------------------------------------------------------------------------**;
PROC SORT DATA=A_B_Combined NODUPKEY
OUT =A_B_Final;
BY ID Date_A Cycle_A Date_B;
RUN;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.