Desktop productivity for business analysts and programmers

Joining tables

Reply
New Contributor
Posts: 2

Joining tables

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


SAS vraag.PNG
Super User
Posts: 19,086

Re: Joining tables

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. 

Super User
Posts: 5,383

Re: Joining tables

Side note: COALESCEC is a data step function.

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

Data never sleeps
Frequent Contributor
Posts: 128

Re: Joining tables

I use coalescec in proc sql all the time.  ?

New Contributor
Posts: 2

Re: Joining tables

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;

Super User
Posts: 5,383

Re: Joining tables

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

Data never sleeps
Contributor
Posts: 46

Re: Joining tables

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.

 

Ask a Question
Discussion stats
  • 6 replies
  • 276 views
  • 0 likes
  • 5 in conversation