BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Frank_K
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

View solution in original post

2 REPLIES 2
LinusH
Tourmaline | Level 20

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
Frank_K
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 724 views
  • 0 likes
  • 2 in conversation