topic Re: Left Join Issue in SAS Procedures

Left Join Issue

jerry898969 — Thu, 18 Mar 2010 16:58:32 GMT

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

Re: Left Join Issue

sbb — Thu, 18 Mar 2010 18:02:00 GMT

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

Re: Left Join Issue

Patrick — Thu, 18 Mar 2010 18:12:13 GMT

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

Re: Left Join Issue

jerry898969 — Thu, 18 Mar 2010 18:31:29 GMT

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

Re: Left Join Issue

Flip — Fri, 19 Mar 2010 12:06:22 GMT

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 ;