topic Re: PROC SQL - Left Join Not Working in SAS Programming

PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 12:24:06 GMT

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.

Re: PROC SQL - Left Join Not Working

SuryaKiran — Wed, 11 Jul 2018 12:46:14 GMT

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;

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Wed, 11 Jul 2018 12:52:36 GMT

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.

Re: PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 13:35:01 GMT

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.

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Wed, 11 Jul 2018 13:42:59 GMT

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

Re: PROC SQL - Left Join Not Working

SuryaKiran — Wed, 11 Jul 2018 14:16:12 GMT

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

Re: PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 15:30:34 GMT

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?

OuTrade

Admin

System Admin/Support Group

Yes

$shisk

UGPS16

Yes

Invnrsch;Slnsast;Cimpnmts

Change

Yes

role composition, owner

Clr

Clearance and Settlements Group

Yes

%fjskff

UG6R45

Change

Yes

role composition

Clsup

Client Support and Onboarding Group

Yes

%0custlui

UG6R45

Change

Yes

role composition

Clspmgr

Client Support and Pledging Manager

Yes

%lkscustlui

UG6R45

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?

ALOPS

Admin

Client Support and Onboarding Group

Yes

%fjskff

UGPS16

Yes

Invnrsch;Slnsast;Cimpnmts

Change

Yes

role composition, owner

Clr

Clearance and Settlements Group

Yes

%0custlui

UG6R95

Change

Yes

role composition

Admin

Client Support and Onboarding Group

Yes

%0custlui

UG6R45

Change

Yes

role composition

Admin

System Admin/Support Group

Yes

%lkscustlui

UGPS29

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?

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Wed, 11 Jul 2018 15:34:56 GMT

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

Re: PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 16:49:32 GMT

Hi @Kurt_Bremser ,

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

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.

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Wed, 11 Jul 2018 17:19:15 GMT

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

Maxim 3: know your data.

Re: PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 17:35:42 GMT

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

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Wed, 11 Jul 2018 18:03:49 GMT

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

Re: PROC SQL - Left Join Not Working

belboy — Wed, 11 Jul 2018 18:08:41 GMT

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

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Thu, 12 Jul 2018 06:08:42 GMT

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.

Re: PROC SQL - Left Join Not Working

belboy — Thu, 12 Jul 2018 13:12:48 GMT

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.

Re: PROC SQL - Left Join Not Working

SuryaKiran — Thu, 12 Jul 2018 13:57:46 GMT

I'm glad that you found the solution. But note that this will keep only one value in the macro variable when you have multiple values.

Check this two approaches where there are multiple values to be accounted.

proc sql noprint;
select name into: Name
from sashelp.class;
quit;

proc sql;
select * from sashelp.class
where name="&Name"
;
Quit;

proc sql noprint;
select quote(strip(name)) into:Name separated by ','
from sashelp.class;
quit;

proc sql;
select * from sashelp.class
where name in (&name)
;
Quit;

Re: PROC SQL - Left Join Not Working

Kurt_Bremser — Thu, 12 Jul 2018 14:04:04 GMT

The situation seems to be like that:

Each new dataset only contains data for a specific group in the existing dataset. From that, I think that adding the

a.'Profile Application'n = b.appname

to the on clause of the left join should also work. But taking the appname from the first obs of the incoming file and using that in the where will also work, as the "solved" message shows.