01-25-2017 09:15 AM
I'm new in using SAS and I have a problem joining 2 tables together:
I have 1 table that contains the following variables: a, b, c, d, e, f
I have another table that contains the following variables: a, b, c, d, e, f, g
I would like to join these tables by a, but when I use a FULL JOIN I got 2 rows with a (a1 and a2) where a2 contains a lot of missing values. When a value is missing in a1 the rest of the variables b, c, d, e, f are also missing, but they were available in my previous table.
I would like to have the series of number put together without losing information. And I would like to have 1 row of a.
In the attachment you can see that number 2441 is only present in a2, but in my previous table b, c, d, e, f where available. So this information is not included when I join the tables.
Can someone help me?
I already tried to use a different join, but the results became so weird. I also tried to use append table and than performe a query builder but that gave me also a lot of missing values that were available before.
By the way: I use SAS EG 7.1
01-25-2017 09:26 AM
Your join is likely fine, but you need to explicitly deal with the remaining variables that have the same name.
If you want to take the value in either look at the COALESCE/COALESCEC functions.
01-25-2017 09:59 AM
Thank you so much!
I tried an example of coalesce found on the internet. But where do I put this function in my program?
CREATE TABLE WORK.SELECTIE AS
SELECT t2.a AS a2,
FROM WORK.SELECTIE3 t1
FULL JOIN WORK.SELECTIE2 ON (t1.a = t2.a);
01-29-2017 09:19 AM
Why not try SAS MERGE, to the two tables:
data <resulttable>; merge <table1> <table2>; by a; run;
This will result in the exact same thing that you are expecting from FULL JOIN.
--Hope it helps.