Solved
New Contributor
Posts: 4

# Merging Issue

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

Accepted Solutions
Solution
‎11-08-2016 03:02 PM
Super User
Posts: 10,850

## Re: Merging Issue

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;

All Replies
Super User
Posts: 6,933

## Re: Merging Issue

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.

Solution
‎11-08-2016 03:02 PM
Super User
Posts: 10,850

## Re: Merging Issue

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;

Frequent Contributor
Posts: 76

## Re: Merging Issue

Hi,

Cud u pls explain these program.

PROC Star
Posts: 1,400

## Re: Merging Issue

What program? Please be more specific

Frequent Contributor
Posts: 76

## Re: Merging Issue

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

PROC Star
Posts: 1,400