BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
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
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Opal | Level 21
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
Pyrite | Level 9
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
Fluorite | Level 6
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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 783 views
  • 0 likes
  • 4 in conversation