Help using Base SAS procedures

Tables not joining correctly - missing values even though they exist

Reply
Contributor
Posts: 42

Tables not joining correctly - missing values even though they exist

Hi SAS Community!

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

So I have these two tables:

TableA

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

TableB

CH_ACCT_NOTRANSACTION_DATETRANSACTION_CODETRANSACTION_AMOUNT
111 13 Jun 20131005100
222 3 Nov 20131005100
333 13 Dec 20131005100
444 3 Sep 20131005100
444 4 Sep 20131234530
444 4 Sep 20131006100

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_NOTRANSACTION_DATETRANSACTION_CODETRANSACTION_AMOUNTOPEN_DATE
111 13 Jun 201310051003 Jun 2013
222 3 Nov 20131005100.
333 13 Dec 201310051002 Jul 2013
444 3 Sep 20131005100.
444 4 Sep 2013123453017 Aug 2013
444 4 Sep 2013100610017 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:

Super Contributor
Posts: 578

Re: Tables not joining correctly - missing values even though they exist

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;

New Contributor
Posts: 2

Re: Tables not joining correctly - missing values even though they exist

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,

Smiley Happy

Ask a Question
Discussion stats
  • 2 replies
  • 229 views
  • 6 likes
  • 3 in conversation