Working out running totals

Super Contributor
Posts: 261

Working out running totals

Hi

I have the following dataset and I want to create a formula that calculates the max profit from a certain amount of capital. The dataset below shows horserace data over a few hours. The field scrapetime is the time the latest data was taken about the race. The outcome is the horse being backed, the return percentage is the expected return on capital, the Time_to_event to the numbers of hours to the event starts, the max stake is the Max_Stake is the maximum amount that can be placed on the horse.

I have two variables: timeto and capital which I have set as follows:

%let timeto = 2; /* max of two hours to event*/

%let capital = 1000; /* max capital of €1000 */

I want the algorithm to do the following

sort by scrapetime /* which is fine

apply timeto restriction i.e. only apply algorithm to rows where the timeto is less than 2 hours /* which is fine

So in the first event below the timeto restriction is met so the max_stake of 52 is placed and the capital_remaining = 1000-52 = 948

Here is the difficult part, i want the algorithm to work through each line but retain information from the previous lines.

So, for example, the second event is after the first event has taken place and the first bet lost so my remaining capital is still 948 (if it has one it would be 948+416= 1364)

So for each row it needs to determine which of the previous races have occurred between this event and the last and adjust the capital accordingly. I'm not sure how to do this. But the description of what I want at each row is

1. Have any previous events finished since the last row

2. If so what is the capital asjustment (this is just +profit if win and no adjustment if lose)

and to keep doing this until it gets to the last row

 Event scrapetime event_time Outcome Return_Perc Time_To_Event Max_Stake Outcome Potential Profit Listowel 15SEP16:16:25 2016/9/15 - 16:40 Jack The Wire 0.02 0.3 52 Lose 416 Ayr 15SEP16:16:51 2016/9/15 - 17:20 Henley 0.01 0.5 18 Win 225 Crayford 15SEP16:16:52 2016/9/15 - 16:57 Lordsbury Dolly 0.02 0.1 15 Lose 44 Listowel 15SEP16:16:57 2016/9/15 - 17:45 Dos Picas 0.01 0.8 13 Lose 26 Ayr 15SEP16:16:57 2016/9/15 - 17:20 Catwilldo 0.04 0.4 629 Lose 4180 Pontefract 15SEP16:17:04 2016/9/15 - 17:10 Queen Of The Stars 0.01 0.1 1188 Win 567 Pontefract 15SEP16:17:34 2016/9/15 - 17:40 Irish Optimism 0.01 0.1 562 Win 821 Chelmsford City 15SEP16:17:34 2016/9/15 - 17:50 Prince Of Cool 0.01 0.3 28 Lose 112 Chelmsford City 15SEP16:17:38 2016/9/15 - 17:50 Prince Of Cool 0.01 0.2 485 Lose 1892 Chelmsford City 15SEP16:17:47 2016/9/15 - 18:20 Free Bounty 0.02 0.6 422 Lose 836 Chelmsford City 15SEP16:17:49 2016/9/15 - 18:20 Free Bounty 0.02 0.5 432 Lose 854 Chelmsford City 15SEP16:17:51 2016/9/15 - 18:20 Free Bounty 0.02 0.5 334 Lose 661 Chelmsford City 15SEP16:17:53 2016/9/15 - 19:50 Paper Faces 0.02 2 806 Lose 2338 Pontefract 15SEP16:17:59 2016/9/15 - 18:10 Bay Mirage 0.01 0.2 129 Win 1071 Pontefract 15SEP16:18:00 2016/9/15 - 18:10 Mon Brav 0.02 0.2 119 Lose 1258 Belmont Park 15SEP16:18:25 2016/9/15 - 18:30 Majestic Jessica 0.04 0.1 37 Win 95 Hove 15SEP16:18:46 2016/9/15 - 19:01 Kilmurry Luke 0.02 0.3 32 Lose 57 Belmont Park 15SEP16:18:48 2016/9/15 - 19:01 Dunk A Din 0.06 0.2 26 Win 192 Belmont Park 15SEP16:18:50 2016/9/15 - 19:01 Dunk A Din 0.06 0.2 18 Lose 133 Newcastle 15SEP16:19:06 2016/9/15 - 19:11 Bramble Diamond 0.03 0.1 74 Win 137 Delaware Park 15SEP16:19:29 2016/9/15 - 19:45 Tizthefastlaine 0.01 0.3 90 Lose 180 Delaware Park 15SEP16:19:31 2016/9/15 - 19:45 Tizthefastlaine 0.02 0.2 104 Win 208 Delaware Park 15SEP16:19:33 2016/9/15 - 19:45 Tizthefastlaine 0.02 0.2 107 Lose 212 Henlow 15SEP16:19:41 2016/9/15 - 19:47 Act Of War 0.02 0.1 13 Win 46
Super User
Posts: 9,599

Re: Working out running totals

Hi,

Its a good idea to post test data in the form of a datastep, you can find code to do this here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Your question seems to reside on carrying data over to subsequent rows, you can do this with retain <vars>; statement.  Set them all to missing or zero in the first instance of the group, then check your if <> logic at each observation and update the retained variables as necessary, thus you have the data to impute other variables.

Super Contributor
Posts: 261

Re: Working out running totals

[ Edited ]

Hi

I've been working on this for a while now (using Lag function) but i'm not getting anywhere.

Bascially, the dataset is sorted by scrape time. They are all bets and assume that after the event the stake is returned to my account. So my capital to start is 10000. In the first row my capital is 10000-stake. In the second row, I need to look back to the first row. Using lag If the first event is over then my stake is returned.

Here is my code:

data WORK.DATASET;
infile datalines dsd truncover;
input stake:32. scrapetime: DATETIME14. eventtime: DATETIME14.;
datalines4;
52,15SEP16:16:25,15SEP16:16:40
18,15SEP16:16:51,15SEP16:17:20
15,15SEP16:16:52,15SEP16:16:57
13,15SEP16:16:57,15SEP16:17:45
117,15SEP16:17:04,16SEP16:15:30
1188,15SEP16:17:04,15SEP16:17:10
562,15SEP16:17:34,15SEP16:17:40
28,15SEP16:17:34,15SEP16:17:50
422,15SEP16:17:47,15SEP16:18:20
806,15SEP16:17:53,15SEP16:19:50
129,15SEP16:17:59,15SEP16:18:10
37,15SEP16:18:25,15SEP16:18:30
32,15SEP16:18:46,15SEP16:19:01
26,15SEP16:18:48,15SEP16:19:01
74,15SEP16:19:06,15SEP16:19:11
90,15SEP16:19:29,15SEP16:19:45
13,15SEP16:19:41,15SEP16:19:47
;;;;

data _NULL_;
if 0 then set dataset nobs=n;
call symputx('nrows',n);
stop;
run;

%let initialcapital=10000;

%macro test;

data test;
set dataset;

if _n_ = 1 then do; running_capital = &initialcapital - stake; end;

else do;

%do look_back=1 %to &nrows;

if lag&look_back.(eventtime) < scrapetime then running_capital = running_capital+lag&look_back.(stake);

%end;

running_capital = running_capital - stake;

%end;
run;

%mend;
%test;

Super User
Posts: 9,599

Re: Working out running totals

Hi,

Sorry, I don;t have time to look at this now, off to 3 day meeting.  I would however suggest you re-assess your logic there.  Why have a datastep to get number of observations, then a macro to loop over this.  Look up the syntax of retain variables.  The logic is pretty straight forward (there are probably many examples on here already):

Sort data in the correct order

At each first group blank retained variables

Check logic items

Update retained values with current values

Should be one datastep.

Discussion stats
• 3 replies
• 252 views
• 0 likes
• 2 in conversation