Date: Thu, 21 Oct 2010 07:48:02 -0700
Reply-To: Denise Kruse <krusesas@YAHOO.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Denise Kruse <krusesas@YAHOO.COM>
Subject: Transaction level processing with rolling window of time
Content-Type: text/plain; charset=iso-8859-1
Hello SAS-L,
I am working on making the following PROC SQL code more efficient (that may mean
removing it from PROC SQL). The query works but slows down significantly when
dealing with more than 20K transactions. As I started to think of different
ways to process this data (set/set, first/last, merge?) it ocurred to me that
some of you may have very creative solutions.
Here is some more detail on the goal:
The transaction data (VISA table) has columns: Account #, Tran Amt, Dt_time and
I want to calculate and display the summary data in the last two columns. The
tricky part is that I only want to sum up transactions that occurred in the last
24 hours. So, it's a rolling window of time.
I mocked up an example below. It shows a number of transactions that took place
on one account.
The two columns I'm trying to append are in bold. For each transaction, they
count the number and total dollar amount of transactions that occurred in the
preceding 24 hours (including the current transaction).
ACCOUNT # Tran Amt Dt_time #of trans in last 24 hours SUM AMT of trans in last
24 hours
11112222 10 01OCT2010:17:51:23 1 10
11112222 21.11 02OCT2010:05:51:24 2 31.11
11112222 14.71 05OCT2010:01:47:01 1 14.71
11112222 48.47 05OCT2010:07:11:41 2 63.18
11112222 91.08 05OCT2010:10:17:58 3 154.26
11112222 35.68 05OCT2010:10:18:51 4 189.94
11112222 18.98 06OCT2010:03:44:37 4 194.21
11112222 42.79 07OCT2010:17:51:30 1 42.79
11112222 32.81 10OCT2010:23:13:31 1 32.81
Here is the current code:
proc sql;
create table tmp2 as
select curr.*,
(select sum(TRANAMT)
from visa prev
where curr.osn=prev.osn and prev.dt_tm between intnx('SECOND',
curr.dt_tm ,-86400) and curr.dt_tm
) as tran_amt_24,
(select count(*)
from visa prev
where curr.osn=prev.osn and prev.dt_tm between intnx('SECOND',
curr.dt_tm ,-86400) and curr.dt_tm
) as tran_num_24
from visa curr;
quit;
Thanks for any ideas you choose to share!
Denise
| 
