10-15-2014 05:02 AM
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
10-15-2014 05:44 AM
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.
Post data and desired result if you need a more exact solution. Thanks
10-15-2014 07:40 AM
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....).
do AccountID=1 to 10;
do _i=1 to 10;
proc sort data=have;
by AccountID date time;
format DateTime _lag_DateTime datetime21.;
if first.AccountID or DateTime-_lag_DateTime>60 then