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
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;
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;
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.