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
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.
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.
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'
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'
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?
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.
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.