Merging Issue

[mandonium]

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

[Astounding]

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.

[Ksharp · Accepted solution]

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;

[molla]

Hi,

Cud u pls explain these program.

[PeterClemmensen]

What program? Please be more specific

[molla]

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_)  

[PeterClemmensen]

Use Call Missing before you output.