Tables not joining correctly - missing values even though they exist

[mjheever]

Hi SAS Community!

This is something I'm not quite getting a grip on.

So I have these two tables:

TableA

CH_ACCT_NO	OPEN_DATE
111	3 Jun 2013
222	14 Jun 2013
333	2 Jul 2013
444	17 Aug 2013

TableB

CH_ACCT_NO	TRANSACTION_DATE	TRANSACTION_CODE	TRANSACTION_AMOUNT
111	13 Jun 2013	1005	100
222	3 Nov 2013	1005	100
333	13 Dec 2013	1005	100
444	3 Sep 2013	1005	100
444	4 Sep 2013	1234	530
444	4 Sep 2013	1006	100

So what I want to do is join these tables with the following code:

proc sql;

create table TableC as

select* from TableB left join TableA on (TableA .CH_ACCT_NO =  TableB .CH_ACCT_NO);

quit;

The problem now is that when I look at Table C only some of the Open_Date values are missing for some reason:

CH_ACCT_NO	TRANSACTION_DATE	TRANSACTION_CODE	TRANSACTION_AMOUNT	OPEN_DATE
111	13 Jun 2013	1005	100	3 Jun 2013
222	3 Nov 2013	1005	100	.
333	13 Dec 2013	1005	100	2 Jul 2013
444	3 Sep 2013	1005	100	.
444	4 Sep 2013	1234	530	17 Aug 2013
444	4 Sep 2013	1006	100	17 Aug 2013

The specific record does exist for both tableA and tableB, so the records are perfectly matched by CH_ACCT_NO.

The format of the specific variables in the tables are the same (with all the variables as numeric).

I have tried a the usual Join statement and Inner Join as well but the same problem remains.

Do you maybe know why this would happen and how to get around this ?

Thank you! :smileygrin: :smileygrin: :smileygrin:

[DBailey]

You might try not using the *..

select

    b.ch_acct_no, b.transaction-date, b.transaction-code, b.transaction_amount, a.open_date

from

     tableB b

     left join tableA a

          on b.ch_acct_no=a.ch_acct_no;

[Sas_Pro]

Hello,

I just ran your query and all the OPEN_DATE column seems fine (no missing observations).

I used the following code.

proc sql;

     create table TableC as

          select b.*, a.OPEN_DATE from TableB as b left join TableA as a on b.CH_ACCT_NO=a.CH_ACCT_NO;

quit;

Your code also results correctly. (The Dates 14 June 2013 and 17 Aug 2013 appreared correctly for 222 and 444)

Regards,