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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1832 views
  • 1 like
  • 5 in conversation