Date:         Wed, 23 Mar 2011 05:28:01 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Counting observations within time period
Comments: To: Monika Nauroth <monika113@GMX.DE>
Content-Type: text/plain; charset=ISO-8859-1

Monika,
Here is a data step solution - which may not be as simple as the
sql solutions suggested. On the other hand it is probably faster by
some orders of magnitude:

%let timediff=300; /* 300 seconds = 5 minutes */

data calculate;
  set test;
  by account;
  daytime=dhms(day,hour(time),minute(time), second(time));
  format daytime datetime20.;
  /* Queue of previous daytimes */
  array _queue(500) 8 _temporary_;
  if first.account then
    n_prev=0;
  else do;
    /* Find new end of queue */
    do n_prev=n_prev+1 to 1 by -1 while(daytime-_queue(n_prev)>&timediff);
      end;
    /* push the queue down */
    if not last.account and n_prev>0 then
      call pokelong(peekclong(addrlong(_queue(1)),n_prev*8),
                    addrlong(_queue(2)));
    end;
  /* Initialize the first element of the queue */
  _queue(1)=daytime;
  retain n_prev;
run;

The variable n_prev will then contain the number of transactions from the
last 5 minutes, or whatever you set the timediff macro variable to.

Regards,
Søren

On Tue, 22 Mar 2011 15:13:19 +0100, Monika Nauroth <monika113@GMX.DE> wrote:

>Hi,
>
>my base coding problem looks like this:
>
>data test;
>   infile datalines delimiter=',';
>   input account $ day ddmmyy8. time:TIME8.;
>   format day ddmmyy8. time time8.;
>   datalines;
>4711,01/01/10,00:01:00
>4711,01/01/10,0:04:00
>4711,01/01/10,00:07:00
>4711,01/02/10,07:20:00
>4712,05/03/0,09:00:00
>;
>run;
>
>I would like to calculate for each observation how many obs in a certain
time periode before can be counted.
>An example: There is a table of mailorders - customer_id, order date and
order time. Now I would like to check how many orders the customer did
within the last 5 minutes, last 15 minutes, last 60day, ....
>
>I would prefer to avoid transposing the dataset (due to perfomance) to get
only one observation for each account, but my try does not have the correct
results:
>
>data calculate (drop=prev);
>set test;
>  BY account;
>  format daytime prev datetime18.;
>  daytime=dhms(day,hour(time),minute(time), second(time));
>  retain n_5min;
>  prev=lag(daytime);
>
>  if first.account then do;
>    n_5min=1;
>  end;
>
>  if NOT FIRST.account then do;
>    min_dif=intck('minute',prev,daytime);
>    if min_dif le 5 then n_5min+1; else n_5min=1;
>  end;
>run;
>
>The 3rd observation says that there have been 3 orders within the last 3
minutes :-(
>
>Has anybody a suggestion how to do this?
>
>Kind regards,
>Monika
>--
>NEU: FreePhone - kostenlos mobil telefonieren und surfen!
>Jetzt informieren: http://www.gmx.net/de/go/freephone