BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mandonium
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

6 REPLIES 6
Astounding
PROC Star

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
Super User
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
Fluorite | Level 6

Hi,

Cud u pls explain these program.

PeterClemmensen
Tourmaline | Level 20

What program? Please be more specific

molla
Fluorite | Level 6
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
Tourmaline | Level 20

Use Call Missing before you output.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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