LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2010, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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

Back to: Top of message | Previous page | Main SAS-L page