BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
azsham
Obsidian | Level 7

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
Meteorite | Level 14

@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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1180 views
  • 6 likes
  • 3 in conversation