Help using Base SAS procedures

Left Join Issue

Reply
Super Contributor
Posts: 400

Left Join Issue

I'm using the following sql to try and get back my data in a specific way.
I want a list of my projects and a access level next to each project that the user has access. If he doesn't I want a missing value. This is the code I am using and it only returns back rows where the pid's are equal like an inner join.

proc sql ;
Select a.pid, b.access
From project a LEFT JOIN UserPrjAccess b on (a.pid = b.pid)
where b.Userid = 1
;
quit ;


I need the output like this
Output dataset
Proj1 01
Proj2 02
Proj3 .
Proj4 .
Proj5 01

I know i'm missing something basic but I can't find it.

Thank you for any help
Super Contributor
Super Contributor
Posts: 3,174

Re: Left Join Issue

Posted in reply to jerry898969
Suggest you also share an input data example, possibly using instream data-points (DATA steps using DATALINES along with requisite INPUT statement) for both of your files WORK.Project and WORK.UserPrjAccess. Also, the WHERE clause is testing a numeric value of 1 - for what purpose in this exercise -- is that an individual's USERID?

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument, this topic/post:

proc sql join processing site:sas.com
Respected Advisor
Posts: 4,173

Re: Left Join Issue

Posted in reply to jerry898969
The where clause filters the result set (and not the input table).

Userid will be NULL for all non-matching records - and therefore get filtered.

HTH
Patrick
Super Contributor
Posts: 400

Re: Left Join Issue

Scott,
Sorry for my question not being as detailed. I will make sure to be more detailed in any post.

Patrick that was it.

Thank you to both.

Jerry
Super Contributor
Posts: 359

Re: Left Join Issue

Posted in reply to jerry898969
As Patrick stated you are filtering in the wrong place. This code will work for you.

proc sql ;
Select a.pid, b.access
From project a LEFT JOIN (select * from UserPrjAccess where Userid = 1 ) b on (a.pid = b.pid)

;
quit ;
Ask a Question
Discussion stats
  • 4 replies
  • 184 views
  • 0 likes
  • 4 in conversation