Merging Issue

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Merging Issue

Here are the tables I'm merging:

 

Table_1

ABC1D1
1aabc123
1adef456
2bghi789
2bjkl987
3cmno654

 

 

Table_2

ABC2D2
1apqr321
2bvwx543

 

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:

 

ABC1C2D1D2
1aabcpqr123321
1adefpqr456321
2bghivwx789543
2bjklvwx987543
3cmno 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:

 

ABC1C2D1D2
1aabcpqr123321
1adef 456 
2bghivwx789543
2bjkl 987 
3cmno 654 

 

Thank you,

Armando


Accepted Solutions
Solution
‎11-08-2016 03:02 PM
Grand Advisor
Posts: 9,567

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,955

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
Grand Advisor
Posts: 9,567

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;

Contributor
Posts: 56

Re: Merging Issue

Hi,

Cud u pls explain these program.

Valued Guide
Posts: 501

Re: Merging Issue

What program? Please be more specific

Contributor
Posts: 56

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

Valued Guide
Posts: 501

Re: Merging Issue

Use Call Missing before you output.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 219 views
  • 1 like
  • 5 in conversation