PROC SQL - Left Join Not Working

[belboy]

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.

[SuryaKiran]

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;

[Kurt_Bremser]

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.

[belboy]

Thanks @Kurt_Bremser 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:

name	address	state	cars
john	555 jolly	ga	f150
mike	444 keys	fl	lexus
lou	333 snow	la	camry
harold	222 frills	ga	civic
greg	111 yodle	ga	pilot
susan	777 sleds	tx	f250
michelle	999 elves	sc	navigator

Table B has fewer obs:

name	address	state
john	555 jolly	ga
greg	111 yodle	ga
harold	222 frills	ga

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.

[Kurt_Bremser]

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

[SuryaKiran]

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

[belboy]

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:

Ref

Appname

Role 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)

Type

Does this Entitlement/Role grant Privileged/Elevated Access (Yes or No)

If Change, what changed?

1

OuTrade

Admin

System Admin/Support Group

Yes

$shisk

UGPS16

Yes

Invnrsch;Slnsast;Cimpnmts

Change

Yes

role composition, owner

2

Clr

Clearance and Settlements Group

Yes

%fjskff

UG6R45

No

Change

Yes

role composition

3

Clsup

Client Support and Onboarding Group

Yes

%0custlui

UG6R45

No

Change

Yes

role composition

4

Clspmgr

Client Support and Pledging Manager

Yes

%lkscustlui

UG6R45

No

Change

Yes

role composition

or on another day Table B could look like this:

Ref

Appname

Role 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)

Type

Does this Entitlement/Role grant Privileged/Elevated Access (Yes or No)

If Change, what changed?

1

ALOPS

Admin

Client Support and Onboarding Group

Yes

%fjskff

UGPS16

Yes

Invnrsch;Slnsast;Cimpnmts

Change

Yes

role composition, owner

2

Clr

Clearance and Settlements Group

Yes

%0custlui

UG6R95

No

Change

Yes

role composition

3

Admin

Client Support and Onboarding Group

Yes

%0custlui

UG6R45

No

Change

Yes

role composition

4

Admin

System Admin/Support Group

Yes

%lkscustlui

UGPS29

No

Change

Yes

role composition

Here is Table A:

Profile Application	Role	Profile Description	SoxCritical	Privileged	Owner	rolecomposition	DESCRIPTION	Conflict
OuTrade	Clspmgr	No Description	TRUE	TRUE	UG6R45	seldistrade	Client Support and Pledging Manager
OuTrade	Opsmgr	No Description	TRUE	TRUE	UG6R45	uhold	Operations Administration Group
OuTrade	MO	No Description	TRUE	TRUE	UG6R45	rqsttrdhist	Middle Office Group
OuTrade	MO	No Description	TRUE	TRUE	UG6R45	showlog	Middle Office Group
ALOPS	Connect	No Description	FALSE	FALSE	FUS332	uhold	Middle Office Group
ALOPS	Connect	No Description	FALSE	FALSE	UL9V42	uhold	Middle Office Group
ALOPS	Connect	No Description	TRUE	TRUE	U4GL3F	showlog	Middle Office Group
BXTRAN	Slimsfx	No Description	TRUE	TRUE	UF442K1	seldistrade	Operations Administration Group
BXTRAN	Slimsfx	No Description	FALSE	TRUE	U4GL3F	seldistrade	Operations Administration Group
BXTRAN	Slimsfx	No Description	TRUE	FALSE	U4GL3F	seldistrade	Operations 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?

[Kurt_Bremser]

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

[belboy]

Hi @Kurt_Bremser , 

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.

[Kurt_Bremser]

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

Maxim 3: know your data.

[belboy]

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

[Kurt_Bremser]

---

@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)

[belboy]

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

[Kurt_Bremser]

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.

[belboy · Accepted solution]

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.