topic Re: Joining tables in SAS Enterprise Guide

Joining tables

Miriammess — Wed, 25 Jan 2017 14:15:19 GMT

Hello,

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

Re: Joining tables

Reeza — Wed, 25 Jan 2017 14:26:08 GMT

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.

Re: Joining tables

LinusH — Wed, 25 Jan 2017 14:58:28 GMT

Side note: COALESCEC is a data step function.

COALESCE works on both numerical and character columns/values in SQL.

Re: Joining tables

Miriammess — Wed, 25 Jan 2017 14:59:16 GMT

Thank you so much!

I tried an example of coalesce found on the internet. But where do I put this function in my program?

PROC SQL;
CREATE TABLE WORK.SELECTIE AS
SELECT t2.a AS a2,
t1.a1,
t2.b,
t2.c,
t2.d,
t2.e,
t2.f,
t1.g
FROM WORK.SELECTIE3 t1
FULL JOIN WORK.SELECTIE2 ON (t1.a = t2.a);
QUIT;

Re: Joining tables

LinusH — Wed, 25 Jan 2017 15:40:27 GMT

On every column in your SELECT clause where you have overlapping columns names, and the column values shouldn't conflict.

Re: Joining tables

MeganE — Thu, 26 Jan 2017 18:04:15 GMT

I use coalescec in proc sql all the time. ?

Re: Joining tables

ankit___gupta — Sun, 29 Jan 2017 14:19:45 GMT

Hi Miriammess,

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.