topic Re: Data Matching problem in SAS Procedures

Data Matching problem

Frank_K — Wed, 29 Jan 2014 16:08:08 GMT

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

Re: Data Matching problem

LinusH — Wed, 29 Jan 2014 16:36:37 GMT

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;

Re: Data Matching problem

Frank_K — Wed, 29 Jan 2014 17:49:14 GMT

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