Desktop productivity for business analysts and programmers

Left join duplicate issue

Reply
Occasional Contributor
Posts: 19

Left join duplicate issue

Hi, I have a left join SQL clause below:

 

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_APPEND_TABLE_SAS7BDAT AS
SELECT DISTINCT 

t1,clientID

t1.ADJ_CREATION_DATE,
t1.CREDIT_AMT,
t2.EFF_DATE,
FROM work.append_table t1 LEFT JOIN WORK.table2 t2 ON (t1.clientID= t2.clientID)
QUIT;

 

For some strange reason, my observation gone up, and check variable EFF_DATE, filter out the missing value on that variable. still the observation is higher than the work.append_table.

 

is there a fix for that?

 

Thanks

Trusted Advisor
Posts: 1,115

Re: Left join duplicate issue

Hi @bohonghong,

 

It seems that your code contains typos (the commas between "t1" and "clientID" and after "EFF_DATE").

 

You probably have duplicate values of clientID in TABLE2. These duplicate groups may or may not include different values of EFF_DATE. So, you should first 

  1. check why there are duplicates
  2. check whether some of them have different values of EFF_DATE
  3. decide how to deal with these duplicates (e.g. select only one observation per clientID from TABLE2 using a selection criterion)

If you decide to select only one observation per clientID from TABLE2, you could replace "WORK.table2" by an inline view, i.e. a SELECT statement to perform the selection from TABLE2.

Super User
Posts: 5,351

Re: Left join duplicate issue

That's twice in two days that we've posted within a minute of each other, and agreed on the issue.  I may have to go back to my old policy of waiting at least an hour before making the first post to a question.  Smiley Happy

 

 

Trusted Advisor
Posts: 1,115

Re: Left join duplicate issue

@Astounding: It would have been even more than twice if I hadn't clicked "Cancel" yesterday when I noticed your reply while I was still writing (and struggling to find the right English words -- as a non-native speaker). Smiley Happy

Please don't delay your replies. I am going to change my policy soon (for external reasons) and concentrate on harder tasks and important corrections.

 

Actually, in the question at hand, our agreement is only partial: I think, if there weren't any duplicates of clientID in TABLE2, duplicates in APPEND_TABLE could not have the effect described by the OP.

Super User
Posts: 5,351

Re: Left join duplicate issue

Have you checked your APPEND_TABLE for multiple observations for the same CLIENT_ID?

 

While you are at it, it wouldn't help to check TABLE2 at the same time.

 

The "fix" depends on what you would like to accomplish.  Most likely you remove duplicates from APPEND_TABLE first, but you have to decide what the right result would be.

Ask a Question
Discussion stats
  • 4 replies
  • 549 views
  • 3 likes
  • 3 in conversation