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 |
Hi,
Its a good idea to post test data in the form of a datastep, you can find code to do this here:
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.
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.