Solved
New Contributor
Posts: 2

# Data Matching problem

Hi All,

My problem seems like it should be easy, but I can't seen to determine the correct solution.

Each employee has restricted accounts they can not access (have1).

What I want to identify from the logs (have2), are those employees who accessed another employee's restricted account.  And the employee whose restricted account was accessed, accessed a restricted account for the employee who accessed their account.

I am trying to identify the scenario where if you access my restricted account, I will access your restricted account to bypass the account control.

The 2 tables are simplified data examples, but the logic for the solution should be the same for my production data.

data have1;

length User R_Accounts \$2;

input User R_Accounts;

datalines;

AA A1

AA A2

AA A3

BB B1

BB B2

BB B3

CC C1

CC C2

CC C3

;

data have2

length user Acct \$2;

input id \$3 user acct \$2 ;

datalines;

001 AA B1

002 AA Z2

003 AA Z3

004 BB A1

005 BB Z1

006 BB C2

007 CC B2

008 CC A2

009 CC Z2

010 AA A1

;

Want

id   user  acct

001  AA    B1

004  BB    A1

006  BB    C2

007  CC    B2

In the above "Want" results, AA accessed a restricted account of BB, and BB accessed an account for AA.

The same for the second pair, BB accessed an account of CC's and CC accessed an account for BB.

Thank you,

Frank

Accepted Solutions
Solution
‎01-29-2014 11:36 AM
Super User
Posts: 5,876

## Re: Data Matching problem

Here a sample code. Not been tested for any M-M situations.

In result, you can chose to keep the information of a match on asingle row, or have them on seprate rows (and then, you probably need som id that hold the match together, no?).

proc sql;

create table step1 as

select have2.*, have1.user as user2

from have2

inner join have1

on have2.acct = have1.r_accounts

;

create table want as

select A.*, b.acct as acct2

from step1 as a inner join step1 as b

on a.user = b.user2 and a.user2 = b.user and a.user not = b.user

;

quit;

Data never sleeps

All Replies
Solution
‎01-29-2014 11:36 AM
Super User
Posts: 5,876

## Re: Data Matching problem

Here a sample code. Not been tested for any M-M situations.

In result, you can chose to keep the information of a match on asingle row, or have them on seprate rows (and then, you probably need som id that hold the match together, no?).

proc sql;

create table step1 as

select have2.*, have1.user as user2

from have2

inner join have1

on have2.acct = have1.r_accounts

;

create table want as

select A.*, b.acct as acct2

from step1 as a inner join step1 as b

on a.user = b.user2 and a.user2 = b.user and a.user not = b.user

;

quit;

Data never sleeps
New Contributor
Posts: 2

## Re: Data Matching problem

Thank you LinusH, this looks like it will work.  Yes, your are correct, on my real data I would want the results on a single row,  one record.

Thanks again,

Frank

🔒 This topic is solved and locked.