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
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
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.
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.
@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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.