Contributor
Posts: 66

# Working out time between two records by unique ID.

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.

SAS Employee
Posts: 340

## Re: Working out time between two records by unique ID.

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.

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.

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

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

Posts: 4,736

## Re: Working out time between two records by unique ID.

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;

Discussion stats
• 2 replies
• 243 views
• 2 likes
• 3 in conversation