05-12-2016 11:34 AM
Hi, I have a left join SQL clause below:
CREATE TABLE WORK.QUERY_FOR_APPEND_TABLE_SAS7BDAT AS
FROM work.append_table t1 LEFT JOIN WORK.table2 t2 ON (t1.clientID= t2.clientID)
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?
05-12-2016 11:56 AM
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
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.
05-12-2016 12:02 PM
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.
05-12-2016 01:41 PM
@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.
05-12-2016 11:58 AM
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.