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
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;
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.
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;
Hi,
Cud u pls explain these program.
What program? Please be more specific
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_)
Use Call Missing before you output.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.