Calculate How Prior Bet Profits/Losses Influence Future Betting

[rick_b]

When a gambler decides how much to bet, their decision is influenced by their prior wager outcomes (perhaps they start to double down if they have sustained losses, for example), and I am trying to examine how NFL gamblers' prior betting profits/losses influence their next-round-of-betting behavior.  I need to sum just the profits/losses from last round of completed NFL events. NFL events occur in distinct blocks (e.g., Sunday at 1:00, 4:00, and 8:00, Monday at 8:00, Thursday at 9:00) and last three hours, but bets can be placed any time from a week before the block up until game time. The dataset I am attaching contains only one gambler but there are many gamblers (each uniquely identified by product_account_id), so the sum needs to begin anew when a new product_account_id is reached. As you can see, things get especially messy with rows 88-103. I would also like to set up a structure where I can sum the profits from the last 2, 3, 4, and 5 rounds of betting. Any help would be greatly appreciated.

data WORK.bets;
infile datalines;
input product_account_id:$10. bet_id:$15. eleven_digit_account_id:$11. net_stake:3. bet_date:mmddyy10.
bet_time:time8. bet_day_of_week:$10. event_date:mmddyy10. event_time:time8.
event_day_of_week:$10. bet_profit:5. desired_output:5.;
format bet_date mmddyy10. bet_time time8. event_date mmddyy10. event_time time8.;

datalines;
12101170 81758848.516 00012101170 2 10/9/2023 20:29 Monday 10/9/2023 20:16 Monday 2 .
12101170 81758939.516 00012101170 3 10/9/2023 20:30 Monday 10/9/2023 20:16 Monday 2.4 .
12101170 81759255.516 00012101170 2 10/9/2023 20:34 Monday 10/9/2023 20:16 Monday 1.68 .
12101170 81760925.516 00012101170 3 10/9/2023 20:48 Monday 10/9/2023 20:16 Monday 2.61 .
12101170 81764937.516 00012101170 3 10/9/2023 21:26 Monday 10/9/2023 20:16 Monday -3 .
12101170 81768542.516 00012101170 3 10/9/2023 22:03 Monday 10/9/2023 20:16 Monday 2.64 .
12101170 82231252.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:01 Sunday 2.73 8.33
12101170 82231251.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82231250.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.46 8.33
12101170 82231253.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:26 Sunday 2.85 8.33
12101170 82231254.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:27 Sunday -3 8.33
12101170 82231345.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 13:05 Sunday 2.46 8.33
12101170 82231346.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82231347.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 16:26 Sunday 2.85 8.33
12101170 82231348.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 16:27 Sunday -3 8.33
12101170 82231466.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82231465.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 13:05 Sunday 2.46 8.33
12101170 82231467.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 16:26 Sunday 2.85 8.33
12101170 82348229.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 9:35 Sunday -3 8.33
12101170 82348234.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:01 Sunday -3 8.33
12101170 82348231.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:01 Sunday 2.61 8.33
12101170 82348230.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:03 Sunday -3 8.33
12101170 82348233.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:04 Sunday 2.55 8.33
12101170 82348235.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:05 Sunday 2.94 8.33
12101170 82348232.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82348267.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:01 Sunday 2.61 8.33
12101170 82348270.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:01 Sunday -3 8.33
12101170 82348266.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:03 Sunday -3 8.33
12101170 82348269.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:04 Sunday 2.55 8.33
12101170 82348271.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:05 Sunday 2.94 8.33
12101170 82348268.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82348305.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:01 Sunday -3 8.33
12101170 82348307.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:01 Sunday 2.85 8.33
12101170 82348308.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:03 Sunday -3 8.33
12101170 82348306.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:05 Sunday 2.94 8.33
12101170 82348304.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82439386.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:04 Sunday -3 8.33
12101170 82439455.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:01 Sunday 2.73 8.33
12101170 82439531.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:05 Sunday 2.73 8.33
12101170 82478787.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:06 Sunday 2.46 32.49
12101170 82478786.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 32.49
12101170 82478788.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 32.49
12101170 82478789.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:27 Sunday -3 32.49
12101170 82478974.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:06 Sunday 2.46 32.49
12101170 82478973.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 32.49
12101170 82478975.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:27 Sunday -3 32.49
12101170 82479827.516 00012101170 6 10/15/2023 16:03 Sunday 10/15/2023 16:26 Sunday 5.46 32.49
12101170 82479826.516 00012101170 6 10/15/2023 16:03 Sunday 10/15/2023 16:27 Sunday -6 32.49
12101170 82482465.516 00012101170 3 10/15/2023 16:07 Sunday 10/15/2023 16:26 Sunday 2.73 32.49
12101170 82493073.516 00012101170 2 10/15/2023 16:34 Sunday 10/15/2023 16:26 Sunday 1.92 32.49
12101170 82550551.516 00012101170 2 10/15/2023 20:57 Sunday 10/15/2023 20:23 Sunday -2 11.22
12101170 82553228.516 00012101170 2 10/15/2023 21:23 Sunday 10/15/2023 20:23 Sunday -2 11.22
12101170 82555259.516 00012101170 2 10/15/2023 21:44 Sunday 10/15/2023 20:23 Sunday -2 11.22
12101170 82646184.516 00012101170 3 10/16/2023 20:25 Monday 10/16/2023 20:16 Monday -3 -6
12101170 82647533.516 00012101170 3 10/16/2023 20:34 Monday 10/16/2023 20:16 Monday -3 -6
12101170 82647545.516 00012101170 6 10/16/2023 20:34 Monday 10/16/2023 20:16 Monday -6 -6
12101170 82899789.516 00012101170 2 10/19/2023 20:23 Thursday 10/19/2023 20:16 Thursday 1.96 -12
12101170 82904693.516 00012101170 3 10/19/2023 20:59 Thursday 10/19/2023 20:16 Thursday 2.64 -12
12101170 82906147.516 00012101170 3 10/19/2023 21:08 Thursday 10/19/2023 20:16 Thursday -3 -12
12101170 83235976.516 00012101170 3 10/22/2023 9:23 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83235974.516 00012101170 3 10/22/2023 9:23 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83236035.516 00012101170 3 10/22/2023 9:24 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83236033.516 00012101170 3 10/22/2023 9:24 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83236190.516 00012101170 3 10/22/2023 9:25 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83237824.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:01 Sunday -3 1.6
12101170 83237827.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83237823.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83237825.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:05 Sunday 2.64 1.6
12101170 83237829.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83237922.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:01 Sunday -3 1.6
12101170 83237924.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83237921.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83237926.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83238000.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:01 Sunday -3 1.6
12101170 83238002.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:02 Sunday -3 1.6
12101170 83237999.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83238004.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83238165.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:01 Sunday -3 1.6
12101170 83238164.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83238170.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83238167.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83238244.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:01 Sunday -3 1.6
12101170 83238249.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83238246.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83238318.516 00012101170 3 10/22/2023 9:39 Sunday 10/22/2023 13:03 Sunday -3 1.6
12101170 83238315.516 00012101170 3 10/22/2023 9:39 Sunday 10/22/2023 16:06 Sunday -3 1.6
12101170 83263216.516 00012101170 6 10/22/2023 11:14 Sunday 10/22/2023 13:02 Sunday -6 1.6
12101170 83395974.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83395975.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83395972.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:26 Sunday 2.55 -63.36
12101170 83395973.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:26 Sunday 2.55 -63.36
12101170 83398125.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83398126.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83398127.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:26 Sunday 2.64 -63.36
12101170 83398128.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:26 Sunday 2.82 -63.36
12101170 83403470.516 00012101170 3 10/22/2023 17:13 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83403472.516 00012101170 3 10/22/2023 17:13 Sunday 10/22/2023 16:26 Sunday -3 -63.36
12101170 83408418.516 00012101170 3 10/22/2023 17:44 Sunday 10/22/2023 16:26 Sunday -3 -63.36
12101170 83417137.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36
12101170 83417136.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:06 Sunday 1.71 -63.36
12101170 83417135.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday -3 -63.36
12101170 83417134.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday 1.89 -63.36
12101170 83417192.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday -3 -63.36
12101170 83540873.516 00012101170 3 10/23/2023 19:09 Monday 10/23/2023 20:16 Monday -3 -24
12101170 83575341.516 00012101170 3 10/23/2023 21:21 Monday 10/23/2023 20:16 Monday -3 -24
12101170 83576079.516 00012101170 3 10/23/2023 21:28 Monday 10/23/2023 20:16 Monday -3 -24
proc print;
run;

[PaigeMiller]

Most of us will not download Excel (or other Microsoft Office) files as they can be a security threat. The proper way to provide data is as text, in the form of working SAS data step code (examples and instructions).

[rick_b]

I have edited the original message to include manually input datalines.

[PaigeMiller]

Can you walk us through the logic and arithmetic for a small part of the data so we can match your desired answers?

As you can see, things get especially messy with rows 88-103. 

Actually, I can't see that, can you explain further?

[rick_b]

For bets in rows 7 through 39 the most recently concluded game was played on 10/9 from approximately 20:00 to 23:00 (games last three hours). So, we sum profits from the first 6 rows and obtain 8.33. In rows 40 through 50 the most recently concluded games are contained on rows 7 through 39, so we sum those profits and obtain 32.49. Etc.... Things get messy starting on row 88 because there (through row 103) we need to sum rows 60, 61, 62, 63, 64, 65, 66, 67, 68, 70, 71, 72, 74, 75, 76, 78, 79, 80, 82, 83, 85, and 87. This amalgamation of rows reflect games starting on 10/22 at approximately 13:00 (and concluding at 16:00). At the point in time at which bets on rows 88 through 103 were made, these represent the most recently concluded games. The Excel spreadsheet that I attached is color coded to clearly show these issues, though I realize it can be risky to open unknown Excel files.

It's easy to establish blocks for game days/game times and I have done this, but this path has not been helpful since I cannot figure out how to match these blocks properly with the relevant bets.

[Patrick]

It's easy to establish blocks for game days/game times and I have done this, ...

It's may-be obvious to you but for me even when looking at your color coded Excel I don't really understand the rules for such grouping.

..., but this path has not been helpful since I cannot figure out how to match these blocks properly with the relevant bets.

If you can explain the rules how to match as clear and simple as possible then I'm sure someone can help with the code. Right now I don't understand the required logic so won't be able to provide any code.

[mkeintz]

It seems to me that you have to provide day/time cutpoints so that we know which bet outcomes are attributed to which day/time blocks.    I.e. for each block, when does it start and when does it end.