BookmarkSubscribeRSS Feed
bohonghong
Fluorite | Level 6

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

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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.

Astounding
PROC Star

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

 

 

FreelanceReinh
Jade | Level 19

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2773 views
  • 3 likes
  • 3 in conversation