Working out time between two records by unique ID.

[Stretlow]

Hello there, could I please ask for some help.

I have a table of records that has the Fields  "AccountID", "AuthDate", "AuthTime","Amount"

What I'm trying to do is calculate how many unique ids have records where the AuthDate and Auth time are within 1 minute

so for example if AccountID 1111 had two records on the 14/10/2014, one at 13:01:56 and one at 13:02:13 then this would be counted, obviously if there was then another on the same day at 13:02:31 then that would also be counted.

Thank you in advance

[gergely_batho]

You need to sort your dataset by AccountID AuthDate AuthTime.

Than with a data step you can process it: at each record you look back 1 record and look ahead 1 record and compare the times.

Look-Ahead and Look-Back - sasCommunity

Some more issues, you need to handle:

  combine date and time into date-time ( dhms() function)

  use inck() to calculate time difference, or simply substract date-times.

  be carefull at the beginning and at the end of an AccountID group

  flag (or delete or keep or count) the records (or the whole AccountID) that satisfy the condition.

useful thead:

https://communities.sas.com/message/217602#217602

Post data and desired result if you need a more exact solution. Thanks

[Patrick]

It's often best to provide some sample data and then explain (show) how the desired result should look like.

Below some code creating such sample data and then an approach of how to get to what you want (or at least what I believe to understand that you want....).

data have;

  attrib

    AccountID length=8

    date format=date9.

    time format=time8.

  ;

  date=today();

  do AccountID=1 to 10;

    do _i=1 to 10;

      time=ceil(ranuni(1)*400)+60*60*10;

      output;

    end;

  end;

run;

proc sort data=have;

  by AccountID date time;

run;

data want;

  set have;

  by AccountID;

  format DateTime _lag_DateTime datetime21.;

  DateTime=sum(Date*86400,Time);

  _lag_DateTime=lag(DateTime);

  if first.AccountID or DateTime-_lag_DateTime>60 then

    ClusterID+1;

run;