topic Re: Left join duplicate issue in SAS Enterprise Guide

Left join duplicate issue

bohonghong — Thu, 12 May 2016 15:34:17 GMT

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

Re: Left join duplicate issue

FreelanceReinh — Thu, 12 May 2016 15:56:42 GMT

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

check why there are duplicates
check whether some of them have different values of EFF_DATE
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.

Re: Left join duplicate issue

Astounding — Thu, 12 May 2016 15:58:05 GMT

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.

Re: Left join duplicate issue

Astounding — Thu, 12 May 2016 16:02:14 GMT

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.

Re: Left join duplicate issue

FreelanceReinh — Thu, 12 May 2016 17:41:40 GMT

@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).

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.