topic Re: Merging Issue in SAS Programming

Merging Issue

mandonium — Thu, 03 Nov 2016 00:08:19 GMT

Here are the tables I'm merging:

Table_1

A	B	C1	D1
1	a	abc	123
1	a	def	456
2	b	ghi	789
2	b	jkl	987
3	c	mno	654

Table_2

A	B	C2	D2
1	a	pqr	321
2	b	vwx	543

Here is my syntax

data merge_1_2;

format A B C1 C2 D1 D2;

merge Table_1 Table_2;

by A B;

run;

Here is my result:

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def	pqr	456	321
2	b	ghi	vwx	789	543
2	b	jkl	vwx	987	543
3	c	mno		654

Now the question. How do I get rid of the duplicate values in column C2 and D2? I want my merged table to look like this:

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def		456
2	b	ghi	vwx	789	543
2	b	jkl		987
3	c	mno		654

Thank you,

Armando

Re: Merging Issue

Astounding — Thu, 03 Nov 2016 01:21:30 GMT

It depends on how much you know about your incoming data. If you can guarantee that TABLE_2 never contains multiple observations for the same A/B combination, it's simple:

if first.B=0 then do;

C2=' ';

D2=' ';

end;

That assumes that D2 is character, which appears to be the case since missing values are printing as a blank, not a dot. But if D2 is actually numeric, you would have to switch the assignment of D2, making it:

D2=.;

If there are other possibilities in the data, such as multiple observations in TABLE_2 that match up with a single observation in TABLE_1, it can still be done. But it takes a bit more sophistication. These three statements could go at the bottom of your DATA step:

output;

call missing(of _numeric_);

call missing(of _character_);

I'm unable to test it right now, but that should work.

Re: Merging Issue

Ksharp — Thu, 03 Nov 2016 03:31:05 GMT

data merge_1_2;
format A B C1 C2 D1 D2;
merge Table_1  Table_2;
by A B;
output;
call missing(of _all_);
run;

Re: Merging Issue

PeterClemmensen — Thu, 03 Nov 2016 09:36:45 GMT

Use Call Missing before you output.

Re: Merging Issue

molla — Thu, 16 Mar 2017 07:27:31 GMT

Hi,

Cud u pls explain these program.

Re: Merging Issue

PeterClemmensen — Thu, 16 Mar 2017 08:54:24 GMT

What program? Please be more specific

Re: Merging Issue

molla — Thu, 16 Mar 2017 10:03:25 GMT

data merge_1_2;
format A B C1 C2 D1 D2;
merge Table_1  Table_2;
by A B;
output;
call missing(of _all_);
run;

In the above program what is use of call missing(of _all_)

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def	pqr	456	321
2	b	ghi	vwx	789	543
2	b	jkl	vwx	987	543
3	c	mno		654

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def		456
2	b	ghi	vwx	789	543
2	b	jkl		987
3	c	mno		654

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def	pqr	456	321
2	b	ghi	vwx	789	543
2	b	jkl	vwx	987	543
3	c	mno		654

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def		456
2	b	ghi	vwx	789	543
2	b	jkl		987
3	c	mno		654

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def	pqr	456	321
2	b	ghi	vwx	789	543
2	b	jkl	vwx	987	543
3	c	mno		654

A	B	C1	C2	D1	D2
1	a	abc	pqr	123	321
1	a	def		456
2	b	ghi	vwx	789	543
2	b	jkl		987
3	c	mno		654