topic Re: Merging data set - Overwrite 'V' value in 'R' for same date in SAS Programming

Merging data set - Overwrite 'V' value in 'R' for same date

Sathish_jammy — Thu, 25 Jul 2019 10:25:34 GMT

Hi Experts,

I want to merge my data in the below-given format. Kindly help me solve it.

data aaa;
input ID date1 mmddyy10. reg$;
format date1 date9.;
cards;
100 02/10/1990 V
100 05/09/1991 V
100 12/03/2000 V
101 05/05/2000 V
101 08/07/2010 V
101 04/03/2015 V
;

data bbb;
input ID date1 mmddyy10. reg$;
format date1 date9.;
cards;
100 02/10/1990 R
100 07/08/1995 R
100 12/03/2000 R
101 01/01/2001 R
101 08/07/2010 R
101 04/03/2015 R
;

Required Output :

ID	date1	reg
100	10-02-1990	V
100	09-05-1991	V
100	08-07-1995	R
100	03-12-2000	V
101	05-05-2000	V
101	01-01-2001	R
101	07-08-2010	V
101	03-04-2015	V

V - Visit; R - Record

If visit_date same as record_date then reg should 'V'

If record_date not in aaa table then that line should be included in the Current table/New Table with reg value of 'R'.

Please let me know If you required clarification.

Thanks in advance!

Re: Merging data set - Overwrite 'V' value in 'R' for same date

PaigeMiller — Thu, 25 Jul 2019 12:31:11 GMT


data ab;
	merge aaa(rename=(reg=reg_a) in=in1) bbb(rename=(reg=reg_b) in=in2);
	by id date1;
	if reg_a='V' then reg='V';
	else if reg_b='R' and not in1 then reg='R';
run;

In this small example, every case is handled by the two IF statements. Are there other possible cases?

Re: Merging data set - Overwrite 'V' value in 'R' for same date

Astounding — Thu, 25 Jul 2019 12:33:48 GMT

Given that both of the data sets are already sorted, you can use:

data want;
merge bbb aaa;
by id date1;
run;

You will see a note in the log since REG appears in both data sets. Ignore it.

Re: Merging data set - Overwrite 'V' value in 'R' for same date

Sathish_jammy — Thu, 25 Jul 2019 13:18:59 GMT

Thank you for your quick support @Astounding @PaigeMiller

Both your code works well. Have a great day!