topic Re: Identify three or more occurrences within seven days by a unique ID in SAS Programming

Identify three or more occurrences within seven days by a unique ID

wylie_ma — Thu, 15 Oct 2020 00:10:37 GMT

This might be a tough one.

I have a dataset showing the date an IP address accessed an Account:

IP, Account, Date

108.950.45, 12345, 5/May/2020

108.950.45, 54321, 6/May/2020

108.950.45, 67890, 10/May/2020

108.950.45, 09876, 11/May/2020

108.950.45, 45879, 11/May/2020

108.950.45, 54686, 30/May/2020

220.540.40, 45879, 05/May/2020

220.540.40, 94532, 11/May/2020

220.540.40, 45879, 11/May/2020

110.640.35, 54873, 05/May/2020

110.640.35, 56454, 05/May/2020

110.640.35, 87654, 20/May/2020

I would like to create a dataset that shortlists any IP address that touched three or more unique accounts within a seven day period. My output from above dataset would hence read:

108.950.45, 12345, 5/May/2020

108.950.45, 54321, 6/May/2020

108.950.45, 67890, 10/May/2020

108.950.45, 09876, 11/May/2020

108.950.45, 45879, 11/May/2020

In the above output there are three more accounts linked to same IP within a seven day window.

220.540.40 would not be in final output as it interacted with two unique Accounts. 110.640.35 would also not appear as it did not interact with three accounts in less than 7 days (it took 15 days).

Any suggestions how to tackle this problem?

Re: Identify three or more occurrences within seven days by a unique ID

novinosrin — Thu, 15 Oct 2020 02:37:26 GMT

Hi @wylie_ma My apologies as I haven't tested as I'm awefully tired after a long day. The following should be close. If not, sure you should have the much needed solution while we folks at eastern time US are asleep. Nonetheless, Please try, test and let me know should you need any further.


data have;
infile cards dsd ;
input IP :$30. Account $ Date	:date9.;
format date date9.;
cards;
108.950.45, 12345, 5/May/2020
108.950.45, 54321, 6/May/2020
108.950.45, 67890, 10/May/2020
108.950.45, 09876, 11/May/2020
108.950.45, 45879, 11/May/2020
108.950.45, 54686, 30/May/2020
220.540.40, 45879, 05/May/2020
220.540.40, 94532, 11/May/2020
220.540.40, 45879, 11/May/2020
110.640.35, 54873, 05/May/2020
110.640.35, 56454, 05/May/2020
110.640.35, 87654, 20/May/2020
;

data want;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("account") ;
   h.definedata ("account","ip", "date") ;
   h.definedone () ;
   dcl hiter hi('h');
 end;
 do until(last.ip);
  set have;
  by ip notsorted;
  if date<=_n then if h.check() ne 0 then do;
   _c+1;
   h.add();
  end;
  if date>_n or last.ip  then do;
   	if _c>=3 then do while(hi.next()=0);
	 output;
	end;
	_c=1;
    _n=date+7;
	h.clear();
	h.add();
  end;
 end;
 drop _:;
run;

Re: Identify three or more occurrences within seven days by a unique ID

wylie_ma — Thu, 15 Oct 2020 02:52:44 GMT

Thanks so much again! I wish there was a button I could use on this page to tip you

Re: Identify three or more occurrences within seven days by a unique ID

PeterClemmensen — Thu, 15 Oct 2020 11:33:30 GMT

What if the pattern for an IP address was like this? There are more than 7 days from 05/May/2020 to 15/May/2020, but both can be a part of a 'within 7 days' streak.

110.640.35, 12345, 05/May/2020
110.640.35, 23456, 09/May/2020
110.640.35, 34567, 11/May/2020
110.640.35, 45678, 15/May/2020

Re: Identify three or more occurrences within seven days by a unique ID

wylie_ma — Tue, 20 Oct 2020 23:56:50 GMT

Hypothetically, if I run add an additional row to dataset (see row 3 below), where IP address accesses the same account within 7 days, the code currently will only pickup the first entry within a seven day range.

data have;
infile cards dsd ;
input IP :$30. Account $ Date :date9.;
format date date9.;
cards;
108.950.45, 12345, 5/May/2020
108.950.45, 54321, 6/May/2020
108.950.45, 54321, 13/May/2020
108.950.45, 67890, 10/May/2020
108.950.45, 09876, 11/May/2020
108.950.45, 45879, 11/May/2020
108.950.45, 54686, 30/May/2020
220.540.40, 45879, 05/May/2020
220.540.40, 94532, 11/May/2020
220.540.40, 45879, 11/May/2020
110.640.35, 54873, 05/May/2020
110.640.35, 56454, 05/May/2020
110.640.35, 87654, 20/May/2020
;

Is there anyway to adjust code so that it will pickup all entries within seven day range, even if it is the same account? See below for desired output. Also, if my date format is datetime20., will code be compatible with this format?

108.950.45 54321 06MAY2020

108.950.45 45879 11MAY2020
108.950.45 67890 10MAY2020
108.950.45 12345 05MAY2020
108.950.45 09876 11MAY2020

108.950.45 54321 13MAY2020

Re: Identify three or more occurrences within seven days by a unique ID

novinosrin — Wed, 21 Oct 2020 13:21:06 GMT

Hi @wylie_ma First off, a repeat occurrence of the same account within a particular interval changes your original stated requirement/objective. However, sure it's possible. Also, if you have datetime values instead of date values, we could extract the date value from a datetime value using a datepart function. It's not a big deal.

Ideally, new requirement is better addressed as independent threads instead all in one. Anyways, Please review your requirement thoroughly and let me know as 13may2020 is outside of of the 1st seven interval i.e.

interval 1: 5may2020- 12may2020 (5+7)

and the next goes like anything higher 12may2020 is found, increment the interval by +7 from that date.

Re: Identify three or more occurrences within seven days by a unique ID

wylie_ma — Wed, 21 Oct 2020 15:50:18 GMT

Thank you Novinisrin, Will do!

Re: Identify three or more occurrences within seven days by a unique ID

RichardDeVen — Fri, 23 Oct 2020 16:22:44 GMT

A SQL query with a counting sub-select can create the desired result set.

Example:

proc sql;
  create index IP on have;

  create table want as 
  select *
  from have as each
  where 3 <= (
    select count(distinct lookup.account)
    from have as lookup
    where lookup.IP = each.IP
      and lookup.date between each.date-7 and each.date+7
    )    
  ;
quit;