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 (December 2010, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 10 Dec 2010 20:19:17 +0000
Reply-To:     "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Keintz, H. Mark" <mkeintz@WHARTON.UPENN.EDU>
Subject:      Re: Rolling Limits Over X number of Days
Comments: To: art297 <atabachneck@GMAIL.COM>
In-Reply-To:  <ebf4c234-4141-4215-a221-0f6fe5e0e9ea@p8g2000vbs.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

OK, see if I've missed anything here.

The only wrinkle is to provide arguments for the two MDY functions such that you are sure that every date in your dataset will be covered. Once that's established you can define an ARRAY (btw, not a _TEMPORARY_ array - you don't want values RETAINed) to hold total approved requests for every date in the range.

%let beg_date=%sysfunc(mdy(1,1,1980)); %let end_date=%sysfunc(mdy(12,31,2020));

data want;

array req {&beg_date:&end_date} ;

do until (last.customer); set have; by customer; if attempt_to_spend > 50 - sum(0,req{day-2},req{day-1},req{day}) then outcome='Deny '; else do; req{day}=sum(req{day},attempt_to_spend); outcome='Accept'; end; drop req: ; output; end; run;

Regards, Mark

> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > art297 > Sent: Friday, December 10, 2010 2:09 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Rolling Limits Over X number of Days > > Noel, > > I'm sure the following can be simplified, a lot, but I think it comes > close do doing what you are trying to accomplish: > > data have; > informat day date9.; > format day date9.; > input Customer Day Attempt_to_spend; > cards; > 1 1DEC2010 20 > 1 2DEC2010 20 > 1 3DEC2010 20 > 1 3DEC2010 10 > 1 4DEC2010 21 > 1 4DEC2010 20 > ; > run; > > data want (drop=past: current_total i j); > array pastdate(100); > array pastamount(100); > do until (last.customer); > set have; > by customer; > if first.customer or i eq 0 then do; > if Attempt_to_spend le 50 then do; > spend=Attempt_to_spend; > i=1; > response='Approve'; > pastdate(i)=day; > pastamount(i)=spend; > end; > else do; > spend=0; > response='Deny'; > i=0; > end; > end; > else do; > if Attempt_to_spend gt 50 then do; > spend=0; > response='Deny'; > end; > else do; > current_total=0; > do j=1 to i; > if day-pastdate(j) le 2 > then current_total+pastamount(j); > end; > if current_total=0 then i=0; > if current_total+Attempt_to_spend le 50 then do; > i+1; > spend=Attempt_to_spend; > response='Approve'; > pastdate(i)=day; > pastamount(i)=spend; > end; > else do; > spend=0; > response='Deny'; > end; > end; > end; > output; > end; > run; > > HTH, > Art > ------------- > On Dec 10, 11:31 am, Spartacus <noel.de.si...@gmail.com> wrote: > > Hi, > > > > I am currently struggling with a SAS query I'm hoping you can help me > > with. > > > > I have a transactional dataset in sas, Basically it contains the > > customer account number, date of the transaction and the amout paid > > for the transaction. > > > > I would like to hightlight all records that would be over a defined > > combined limit, over a defined number of days. > > > > For example, I want to highlight all records where a customer spent > > over 50 in a 3 day rolling period. > > > > Customer 1, Day 1, 20 > > Customer 1, Day 2, 20 > > Customer 1, Day 3, 20 - Deny > > Customer 1, Day 3, 10 - Accept > > Customer 1, Day 4, 21 - Deny > > Customer 1, Day 4, 20 - Accept > > > > Any ideas would greatly appreciate any help!!!


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