Left Join Issue

[jerry898969]

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

[sbb]

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

[Patrick]

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

[jerry898969]

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

[Flip]

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 ;