Having to guess what an event might be is and indicator that perhaps you don't have all the data. Or you have a research question phrased in such a way that the data will not support a resolution.
Lets consider one approach: duration of time between "events". I am not sure that the "bet date" actually contributes much to this but then I have no understanding of the actual data as it feels very incomplete (what was actually bet on is missing).
This might be one way to look at intervals.
Note creation of datetime valued variables in the data step with the DHMS function and much smaller set just as an example.
data WORK.bets (drop=eleven_digit_account_id);
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.;
*Note: games (aka 'events') last three hours from start to finish;
*Note: there are many gamblers, each with a unique product_account_id, so summing needs to begin anew when a new product_account_id arrives;
bet_dt = dhms(bet_date,0,0,bet_time);
event_dt = dhms(event_date,0,0,event_time);
format bet_dt event_dt datetime20.;
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 *the Monday events have expired and the payouts are now known, desired_output is the sum of the first six rows of bets*
12101170 82231251.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.73 8.33 *the last known event outcomes were again last Monday, same calculation*
12101170 82231250.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.46 8.33 *same here*
12101170 82231253.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:26 Sunday 2.85 8.33 *same here*
12101170 82231254.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:27 Sunday -3 8.33 *same here*
12101170 82231345.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 13:05 Sunday 2.46 8.33 *same here*
;
proc sort data=work.bets;
by product_account_id event_dt;
run;
data work.example;
set work.bets (drop=bet_date bet_time bet_day_of_week event_date event_time desired_output event_day_of_week ) ;
by product_account_id event_dt;
retain Event_ID;
event_time_dif=dif(event_dt);
if first.product_account_id then event_id=1;
/*event_time_dif is number of seconts between "events".
3600 would be one hour for example
*/
else if event_time_dif>3600 then event_id+1;
run;
This is an attempt to "group" events together as that seems to be part of what you are doing. It just uses a different metric.
You seem to have a fair amount of data. You might be able to model the Event_time_dif per Product_account_id value (if that represents a person) to see the likely distribution of time between events (or bets). So you might take a pass through the data to develop a per person interval to use in setting a boundary between events.
Any way, IF such an event_id can be generated this way then summing is EASY (once the Event_ID is decided upon):
proc summary data=work.example nway;
by product_account_id Event_id;
var bet_profit;
output out=work.profit_sum sum=Profit_sum;
run;
Your "example" kept saying things that are not programmable in a general sense.
I would be tempted to play around with both an Event_time and a Bet_time interval to see about what is likely to represent an "event".
What the second data set does, assuming the base data is sorted correctly:
The BY statement adds automatic variables that indicate whether an observation is the first (or last) of a BY group. These are only available in a data step and are not added to the data. The values are 1 (if the first or last) or 0 otherwisse and as such can be used as logical variables. SAS treats 1 as true and 0 as false. The main purpose here is to reset the event counter for a new 'product_id'.
The DIF function returns the difference between the current value of the variable that in the previous observation. So with a single datetime valued variable we have number of seconds between "event" times.
I am supposing that larger differences are more likely to indicate a different "event". Finding a boundary for consistency is a chore.
The proc summary creates a total based on the product_id and the Event_id. If you need that value on each observation of your original data then you would merge (or join) it back base on the product_id Event_id.
I suspect that your analysis might be doable with just the product and event ids unless there is something else not stated yet.
If this were my project and I was getting paid to analyze this I would suggest to whoever provided this data that perhaps there is another bit of data that identifies the "event" and suggest that should be included instead of having to guess what is a related group of observations.
Click image to register for webinar
