merge on two possibilities of one conditions

[azsham]

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.

[jimbarbour · Accepted solution]

@azsham,

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;