topic Re: Left join not keeping all records from left hand table in SAS Data Science

Left join not keeping all records from left hand table

eagles_dare13 — Wed, 27 Aug 2014 00:47:01 GMT

I have a left join like this:

proc sql;

select * from a left join b on

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

;

quit;

Basically join a to b on code if it is non null in A else join A to B on code2.

Problem is that A has 100 rows but output has 90 rows only.

Why is this?

Re: Left join not keeping all records from left hand table

PGStats — Wed, 27 Aug 2014 02:23:01 GMT

Hmm puzzling...

I tried toying with a small example and can't get the join to drop obs from A. Can you post a trimmed down version of your data that causes at least one obs from A to be dropped from the join result? - PG

Re: Left join not keeping all records from left hand table

Reeza — Wed, 27 Aug 2014 03:02:38 GMT

<> is not NOT EQUALS in SAS, it's MAX I believe.

Try using NE.

Re: Left join not keeping all records from left hand table

PGStats — Wed, 27 Aug 2014 03:09:26 GMT

, when you run the query as written you get:

150 proc sql;

151 select * from a left join b on

152 (

153 case when a.code <> "" then a.code = b.code

154 else a.code2 = b.code2

155 end

156 )

157 ;

NOTE: The "<>" operator is interpreted as "not equals".

NOTE: The execution of this query involves performing one or more Cartesian product

joins that can not be optimized.

158 quit;

In the small examples I tried, the join worked as expected.

Re: Left join not keeping all records from left hand table

stat_sas — Wed, 27 Aug 2014 03:41:55 GMT

Try two joining conditions separately to check the overlap.

proc sql;

select * from a left join b on

a.code = b.code ;

quit;

proc sql;

select * from a left join b on

a.code2 = b.code2 ;

quit;

Re: Left join not keeping all records from left hand table

RW9 — Wed, 27 Aug 2014 08:42:16 GMT

Hi,

I would agree with stat@sas, check the data first. Personally I wouldn't go with the kind of coding which has case statements in a join like that. Me I would (without being able to test as having no test data) do:

proc sql;

create table WANT as

select *

from A

left join B

on (A.CODE <> "" and A.CODE=B.CODE)

or (A.CODE="" and A.CODE2=B.CODE2);

quit;

Re: Left join not keeping all records from left hand table

jakarman — Wed, 27 Aug 2014 12:40:36 GMT

I the OP's question involving a RDBMS? The Null in a RDBMS (three value logic) is not the same as a missing in SAS. The space as missing value is assumed to be logical equal and can cause some weird effects

Seeing the "<>" operator it could be translated a NE. What says it is producing a "Cartesian product". Theoretically that can solve anything but is assuming unlimited speed and unlimited other resources when the data grows.
With some tests using sas-datasets I can get more records then the original (a count). Did not succeed in getting in less records.

Re: Left join not keeping all records from left hand table

eagles_dare13 — Wed, 27 Aug 2014 14:53:45 GMT

Thanks. Posting the data is not possible ...i will get into all kinds of data confidentiality issues.

I did this in DI studio and i saw DIS rewrote the code like:

proc sql;

select * from a left join b on

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

where

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

;

quit;

Can that be a possible reason?

Re: Left join not keeping all records from left hand table

RW9 — Wed, 27 Aug 2014 15:05:16 GMT

Well, I suppose there's a good illustration of why "Visual Coding" isn't necessarily the best way to do coding. What it appears to be doing is to work out what your doing wrong and try to fix it. My advice, think about what you want out at the end, where that data will come from, then manipulations involved. Then its simply a matter of putting the bits together.

Re: Left join not keeping all records from left hand table

jakarman — Wed, 27 Aug 2014 15:40:25 GMT

The di generation of code does not given an answer whether is an external RDBMS or not. It is often generating common SAS code.
The effects of an implicit pass through SQL translation is not shown. Is it an exterenal DBMS?

The number of records is relative low (100/90). Is there a test to define and sashelp.datasets that shows the effect.
Did the code run and where there special messages (log)?

Re: Left join not keeping all records from left hand table

PGStats — Wed, 27 Aug 2014 16:40:40 GMT

Yes, of course, the WHERE clause will eliminate the cases where there is no match on CODE and on CODE2. Compare results from the following:

data a;

length code code2 $1;

input code code2 x;

datalines;

a a 1

b . 2

. c 3

. . 4

d . 5

. d 6

d c 7

b d 8

;

data b;

length code code2 $1;

input code code2 x;

datalines;

a a 1

b . 2

. c 3

. . 4

;

title "Original query";

proc sql;

select * from a left join b on

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

;

quit;

title "Generated query";

proc sql;

select * from a left join b on

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

where

(

case when a.code <> "" then a.code = b.code

else a.code2 = b.code2

end

)

;

quit;

Re: Left join not keeping all records from left hand table

jakarman — Wed, 27 Aug 2014 18:00:41 GMT

Pgstars I did a same like test. but kept the numbers separated and ordered them on the first table.

Result:

a a 1 a a 1

b 2 b 2

c 3 c 3

4 b 2

4 4

d 5 .

d 6 .

d c 7 .

b d 8 b 2

No one in the input records of the table a is missing, it is a left-join. One repetition on each side 3 non joined.

Re: Left join not keeping all records from left hand table

PGStats — Wed, 27 Aug 2014 18:20:23 GMT

That's the result I get from the first query above. The second query returns only 6 records in my tests. Records where x = 5, 6, and 7 are missing. - PG

Re: Left join not keeping all records from left hand table

jakarman — Wed, 27 Aug 2014 18:45:42 GMT

The first code of eagles dare is joining on several conditions in one pass. That was my the focus on.

The second query as generated by DI is different the results are (correct indicated by PG) :

a a 1 a a 1

b 2 b 2

c 3 c 3

4 b 2

4 4

b d 8 b 2

Re: Left join not keeping all records from left hand table

jakarman — Wed, 27 Aug 2014 19:02:10 GMT

Ok question is not on the original Sql querey but what DI studio is doing with joins and generating code.

SAS(R) Data Integration Studio 4.7: User's Guide In the mentioned case it should not generated the second part with the where.