DATA Step, Macro, Functions and more

PROC SQL - Left Join Not Working

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

PROC SQL - Left Join Not Working

[ Edited ]

SAS Community,

I have two tables that I'm joining together:

Table A has 1353 observations

Table B has 1352 observations

 

I want to keep all observations in table A and only bring in (left join) observations from Table B where they match.  Unfortunately, instead of getting all 1353 obs from Table A (since this is a left join), I'm only getting 1352 obs in my final table.  Because Table A is the first table, I should get all obs from Table A and only obs from Table B if they match, correct? 

 

My code below:

 

PROC SQL;
create table RoleStatusRpt as 
select a.*,
b.'Role Name'n,
b.'business friendly description'n,
b.rolecompfinal,
b.'Role Owner RACF'n,
b.appname,
b.'SoD conflict'n as SOD_Conflict,
b.'SOX Critical (Yes or No)'n,
b.'Does this Entitlement/Role grant'n,

case when a.role ne b.'Role Name'n then "No Match" end as RoleStatus,

case when a.Description ne b.'business friendly description'n then "No Match" end as BFD_Status,

case when b.'SOX Critical (Yes or No)'n = "Yes" and a.soxcritical = "false" or b.'SOX Critical (Yes or No)'n = "No" and a.soxcritical = "true" then "No Match" END AS SOXStatus,

case when a.rolecomposition ne b.rolecompfinal then "No Match" end as CompositionStatus,

case when a.conflict ne b.'SoD conflict'n then "No Match" end as SODConflictStatus,

case when a.owner ne b.'Role Owner RACF'n then "No Match" end as RACFOwnerStatus,

case when b.'Does this Entitlement/Role grant'n = "Yes" and a.privileged = "false" then "No Match" end as PriviledgeStatus


from qa2.sailpoint_role_final as a
left join app as b
on (a.role = b.'Role Name'n and
a.rolecomposition = b.rolecompfinal and 
a.owner = b.'Role Owner RACF'n)
where a.'Profile Application'n = b.appname
;

 

Instead of getting a total of 1353 obs - my final table has 1352 obs.  What am I missing here?

 

SAS Log:

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
expression.
NOTE: Table ROLESTATUSRPT created, with 1352 rows and 24 columns.


Accepted Solutions
Solution
a week ago
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

The solution came to me in a dream - call symput!  Having SAS assign a macro based on a variable within a data step (or in this case Proc Sql).

 

So the code I used to solve the problem is here:

 

proc sql noprint;
select appname
into :appname
from &app; quit;

%let appname = &appname;

 

This allowed SAS to create and assign a dynamic value to a variable that will be used later in the code.  So instead of using the incorrect method of  - where a.'# application'n = b.appname;  I added the macro to change this "where" statement into - where a.'# application'n = "&appname"; 

 

Hope this helps anyone else who may encounter a problem similar to this.

View solution in original post


All Replies
Valued Guide
Posts: 597

Re: PROC SQL - Left Join Not Working

That might be because of the where clause. Don't expect all the records if you have a where clause.

 

Check this example with and without the where clause.

data table1;
input id name $;
datalines;
1 A
2 A
3 A
4 A
;
run;

data table2;
input id name2 $;
datalines;
1 A
2 A
4 a
;
run;

proc sql;
select a.*,b.name2
from table1 a
left join table2 b
on a.id=b.id
where a.name=b.name2
;
quit;
Thanks,
Suryakiran
Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working

[ Edited ]

Look here:

where a.'Profile Application'n = b.appname

I have a strong inkling this is the cause.

Since you only have 1352 obs in B, there's one obs in A that does not get a match, and b.appname will be missing, causing this condition to be false.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

Posted in reply to KurtBremser

Thanks @KurtBremser and @SuryaKiran- can you help me take it a step further?

 

Now that I know this is the error (residing in the where clause), how do I tell SAS to search for the value in b.appname?  See what I mean in the example below...I changed the column names for simplicity.

 

For example and simplicity, Table A has several obs:

nameaddressstatecars
john555 jollygaf150
mike444 keysfllexus
lou333 snowlacamry
harold222 frillsgacivic
greg111 yodlegapilot
susan777 sledstxf250
michelle999 elvesscnavigator

 

Table B has fewer obs:

nameaddressstate
john555 jollyga
greg111 yodlega
harold222 frillsga

 

If  the value of "state" is dynamic or changes in Table B, how do I get all instances of Table A, where Table B matches?    I only want the values to compare when they match Table A.  This is how I would write the code. 

 

proc sql:

create table test as 

    select a.*,

               b.name

               b.state

from table A

left join table B

on (a.name = b.name)

where a.state = b.state; 

 

But now that I know this is incorrect, how do I get all obs from table A and only those from Table B that match?  I can't simply put where a.state = "ga" because I don't have time to open Table B to see the value of state and enter it into the code.  I want SAS to do it automatically.

 

So if Table A has 100 obs that matches state "ga" and only 10 obs that matches "ga" in table B, then I want all 100 obs and only those 10 from table B.  

 

If Table A has 150 obs that matches "fl" and only 40 obs that matches "fl" in table B, then I want all 150 from table A and only those from table B that match.

 

 

Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working

Just move the condition from the where to the on clause.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 597

Re: PROC SQL - Left Join Not Working

Not sure what your trying to do. Can you be more specific, provide some sample data you have and the output your expecting. 

Thanks,
Suryakiran
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

Posted in reply to SuryaKiran

So this is code I created - and can be used by my entire team.  Table B is not a static table... meaning, it can be a different table (although it has the same columns) but we rename it as Table B so that we can compare it to our master table, Table A.  This is explained in detail below:

 

For example, on any day, Table B could look like this:

 

RefAppnameRole Name Business Friendly Description SOX Critical
(Yes or No)
Role Composition:
Semicolon-separated list 
SoD conflict:
Semicolon-separated list
Role Owner
RACF
INHERITANCE:
Is the role "Derived"?
If Inheritance Is Yes, What does the role Inherit From? (Semicolon separated list)TypeDoes this Entitlement/Role grant Privileged/Elevated Access (Yes or No)If Change, what changed?
1OuTradeAdminSystem Admin/Support GroupYes$shisk UGPS16YesInvnrsch;Slnsast;CimpnmtsChangeYesrole composition, owner
2 ClrClearance and Settlements GroupYes%fjskff UG6R45No ChangeYesrole composition
3 ClsupClient Support and Onboarding GroupYes%0custlui UG6R45No ChangeYesrole composition
4 ClspmgrClient Support and Pledging Manager Yes%lkscustluiUG6R45No ChangeYesrole composition

 

or on another day Table B could look like this:

 

RefAppnameRole Name Business Friendly Description SOX Critical
(Yes or No)
Role Composition:
Semicolon-separated list 
SoD conflict:
Semicolon-separated list
Role Owner
RACF
INHERITANCE:
Is the role "Derived"?
If Inheritance Is Yes, What does the role Inherit From? (Semicolon separated list)TypeDoes this Entitlement/Role grant Privileged/Elevated Access (Yes or No)If Change, what changed?
1ALOPSAdminClient Support and Onboarding GroupYes%fjskff UGPS16YesInvnrsch;Slnsast;CimpnmtsChangeYesrole composition, owner
2 ClrClearance and Settlements GroupYes%0custlui UG6R95No ChangeYesrole composition
3 AdminClient Support and Onboarding GroupYes%0custlui UG6R45No ChangeYesrole composition
4 AdminSystem Admin/Support GroupYes%lkscustluiUGPS29No ChangeYesrole composition

 

 

 

Here is Table A:

Profile ApplicationRoleProfile DescriptionSoxCriticalPrivilegedOwnerrolecompositionDESCRIPTIONConflict
OuTradeClspmgrNo DescriptionTRUETRUEUG6R45seldistradeClient Support and Pledging Manager
OuTradeOpsmgrNo DescriptionTRUETRUEUG6R45uholdOperations Administration Group
OuTradeMONo DescriptionTRUETRUEUG6R45rqsttrdhistMiddle Office Group
OuTradeMONo DescriptionTRUETRUEUG6R45showlogMiddle Office Group
ALOPSConnectNo DescriptionFALSEFALSEFUS332uholdMiddle Office Group
ALOPSConnectNo DescriptionFALSEFALSEUL9V42uholdMiddle Office Group
ALOPSConnectNo DescriptionTRUETRUEU4GL3FshowlogMiddle Office Group
BXTRANSlimsfxNo DescriptionTRUETRUEUF442K1seldistradeOperations Administration Group
BXTRANSlimsfxNo DescriptionFALSETRUEU4GL3FseldistradeOperations Administration Group
BXTRANSlimsfxNo DescriptionTRUEFALSEU4GL3FseldistradeOperations Administration Group

 

So if on Monday my Table B looks like my first sample(above), I want all records from Table A and only those from Table B that match.

 

If on another day my Table B looks like my second sample above, I want all records from Table A and only those form Table B that match.

 

I'd like SAS to search for all records in Table A, that equal the variable "Appname" listed in Table B, and left join Table B where there is a match.  How do I do this?  does this example help?

Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working

Try what I suggested (move the condition into the on)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

Posted in reply to KurtBremser

Hi @KurtBremser , 

I tried the following but my records increased to 4937 obs.

 

PROC SQL;
create table RoleStatusRpt as 
select a.*,
b.'Role Name'n,
b.'business friendly description'n,
b.rolecompfinal,
b.'Role Owner RACF'n,
b.appname,
b.'SoD conflict'n as SOD_Conflict,
b.'SOX Critical (Yes or No)'n,
b.'Does this Entitlement/Role grant'n,

case when a.role ne b.'Role Name'n then "No Match" end as RoleStatus,

case when a.Description ne b.'business friendly description'n then "No Match" end as BFD_Status,

case when b.'SOX Critical (Yes or No)'n = "Yes" and a.soxcritical = "false" or b.'SOX Critical (Yes or No)'n = "No" and a.soxcritical = "true" then "No Match" END AS SOXStatus,

case when a.rolecomposition ne b.rolecompfinal then "No Match" end as CompositionStatus,

case when a.conflict ne b.'SoD conflict'n then "No Match" end as SODConflictStatus,

case when a.owner ne b.'Role Owner RACF'n then "No Match" end as RACFOwnerStatus,

case when b.'Does this Entitlement/Role grant'n = "Yes" and a.privileged = "false" then "No Match" end as PriviledgeStatus


from qa2.sailpoint_role_final as a
left join app as b
on (a.role = b.'Role Name'n and
a.rolecomposition = b.rolecompfinal and 
a.owner = b.'Role Owner RACF'n and

a.'Profile Application'n = b.appname)
/*where a.'Profile Application'n = b.appname*/
;

 

SAS Log:

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
expression.
NOTE: Table WORK.TEST1ROLESTATUSRPT created, with 4937 rows and 24 columns.

Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working

Which means you have multiple matches from both datasets and get a cartesian product.

Maxim 3: know your data.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

Posted in reply to KurtBremser

OK - so can't be done?  surely there has to be a way...

Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working


@belboy wrote:

OK - so can't be done?  surely there has to be a way...


Yes. Know your data. From that you can devise ways to prepare your datasets for the join so that you don' get duplicates (summing up, select distinct etc)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

SAS community - let me know if the following makes sense.  Looking for a helpful solution.

Super User
Posts: 10,280

Re: PROC SQL - Left Join Not Working

Post examples for your SAS data in a usable way (data steps with datalines). See my footnotes for help in converting datasets to data steps, and how to post code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
a week ago
Contributor
Posts: 37

Re: PROC SQL - Left Join Not Working

The solution came to me in a dream - call symput!  Having SAS assign a macro based on a variable within a data step (or in this case Proc Sql).

 

So the code I used to solve the problem is here:

 

proc sql noprint;
select appname
into :appname
from &app; quit;

%let appname = &appname;

 

This allowed SAS to create and assign a dynamic value to a variable that will be used later in the code.  So instead of using the incorrect method of  - where a.'# application'n = b.appname;  I added the macro to change this "where" statement into - where a.'# application'n = "&appname"; 

 

Hope this helps anyone else who may encounter a problem similar to this.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 115 views
  • 1 like
  • 3 in conversation