topic Re: Keeping all rows that don't meet Where condition in SAS Programming

Keeping all rows that don't meet Where condition

HSM_88 — Thu, 27 May 2021 21:00:08 GMT

Hello,

Is there a way to keep all of the rows of an input file, even if they don't meet the Where condition?

Example:

Input File:

Customer ID

123

456

789

Need:

Customer ID	Assigned Team Member
123
456	Jane Doe
789

Here is what I have so far, but of course it is only returning the row that meets the Where clause.

SELECT DISTINCT
t1.CustomerID
t2.AssignedTeamMember
FROM WORK.Input t1
LEFT JOIN Team.Names t2 ON (t1.Customer_ID = t2.CustomerID)
WHERE t2.AssignedTeamMember NOT IS MISSING

Re: Keeping all rows that don't meet Where condition

Tom — Thu, 27 May 2021 21:07:09 GMT

Why did you include the WHERE clause at all?

The LEFT JOIN part will include the observations from the "left" dataset even when there is no matching observation from the "right" dataset.

Re: Keeping all rows that don't meet Where condition

Reeza — Thu, 27 May 2021 21:15:10 GMT

No. The left join will ensure that all records from the left table are included. Perhaps you need a COALSECE() instead? Depends on exactly what you're trying to work around.

Re: Keeping all rows that don't meet Where condition

HSM_88 — Fri, 28 May 2021 12:09:14 GMT

I see what you're saying. I realize now that I forgot to add the other criteria is that status of the case has to be "open", otherwise the query will pull the customer's entire history of past assignments. I only need the names of the assigned team members if the case status is open.

SELECT DISTINCT 
t1.CustomerID 
t2.AssignedTeamMember 
FROM WORK.Input t1 
LEFT JOIN Team.Names t2 ON (t1.Customer_ID = t2.CustomerID) 
WHERE t2.CaseStatus = 'open'

Re: Keeping all rows that don't meet Where condition

HSM_88 — Fri, 28 May 2021 12:11:38 GMT

I'm trying to work around pulling in the customer's entire history of past assignments. The database includes the closed cases and I only need the open cases. I forgot to include that part in the original post. Below is the updated query.

SELECT DISTINCT 
t1.CustomerID 
t2.AssignedTeamMember 
FROM WORK.Input t1 
LEFT JOIN Team.Names t2 ON (t1.Customer_ID = t2.CustomerID) 
WHERE t2.CaseStatus = 'open'

Re: Keeping all rows that don't meet Where condition

Tom — Fri, 28 May 2021 12:40:09 GMT

Including a field from the "right" table in the WHERE criteria converts your left join into an inner join.

Is that what you want? Only the records that are in both tables?

Re: Keeping all rows that don't meet Where condition

HSM_88 — Fri, 28 May 2021 12:46:47 GMT

I'd like the keep all of the rows from the input file and pull the data from the 2nd table if it meets the where criteria. So all 5000+ rows of the input file show and if there is an open case with an assigned team member, it'll have that person's name. If not, it will just be blank. Sorry if I'm not explaining it well. I'm very new to SAS

Re: Keeping all rows that don't meet Where condition

Sajid01 — Fri, 28 May 2021 12:47:55 GMT

Hello @HSM_88 your code should work with a small correction. You are missing a comma after t1.CustomerID.
Second the where clause removes all rows where the case status is not open from the output..

So if you want to have all customer ID's from the Input table, then the where clause needs to be dropped.

Re: Keeping all rows that don't meet Where condition

Tom — Fri, 28 May 2021 13:00:24 GMT

Then move the additional criteria into the ON clause instead of adding a WHERE clause.

SELECT DISTINCT 
  t1.CustomerID 
, t2.AssignedTeamMember 
FROM WORK.Input t1 
LEFT JOIN Team.Names t2
 ON (t1.Customer_ID = t2.CustomerID) 
  and (t2.CaseStatus = 'open')

That will get all of the CUSTOMERID values from INPUT matched with zero or more ASSIGNEDTEAMMEMBER values from TEAM.NAMES.

Re: Keeping all rows that don't meet Where condition

HSM_88 — Fri, 28 May 2021 13:01:41 GMT

This worked! Thank you very much. Exactly what I needed.