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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.