I have written the macro below, but am new at this. I have run this for one runner but need to do this for 12 000 runners. So I probably need to do this in a do loop.
The 1st data set a contains the times each runner passes over 6 time mats during a race (time_lynn--time_fini. I selected one runner's times (runner 54915)
The 2nd data set ww contain the weather data measured on the day and this data is merged with the runner data to provide the weather values (WBGT) at each of the 6 mats. The 6 WBGT values form a curve for each runner and then the Area under the curve (AUC) is calculated for each runner into his dataset auc54915. My program produce the correct AUC value for an individual runner.
The AUC datasets needs to be combined for all 12 000 runners.
***********************************MACRO FROM HERE*********************************;
%macro wbgt(runner=54915);
****SELECT ONE FINISHER*********;
data one;set a(keep=race_number time_lynn--time_fini);
if race_number=&runner;
drop race_number;
run;
*****TRANSPOSE DATA FOR SELECTED RUNNER***********;
proc transpose data=one prefix=time out=onet;
run;
****RUNNER TIMES AT EACH OF THE 6 WEATHER STATIONS*********;
data onett;set onet(rename=time1=time);
if _NAME_='time_wins' then delete;
if _NAME_='time_lynn' then namen=1;
if _NAME_='time_cato' then namen=2;
if _NAME_='time_drum' then namen=3;
if _NAME_='time_pine' then namen=4;
if _NAME_='time_sher' then namen=5;
if _NAME_='time_fini' then namen=6;
drop _NAME_;
run;
proc sort data=onett;
by namen time;
run;
*************COMBINE THE INDIV RUNNER MAT TIMES WITH THE WEATHER@EACH MAT TIME;
proc sql;
create table wbgt_6indiv&runner as
select a.time, a.namen, b.namen, b.wbgt, b.time as wbgt_t format=time8.2
from onett a
left join
ww b
on a.namen=b.namen
where b.time>=a.time
group by a.time,a.namen
having abs(a.time-b.time)=min(abs(a.time-b.time))
order by a.namen, b.namen descending;
quit;
***CALCULATE THE AUC FOR THE SELECTED RUNNER;
data auc&runner;set wbgt_6indiv&runner;
runner=&runner;
retain sum 0;
sum_wbgt=((lag(wbgt)+wbgt)/2)*(wbgt_t-lag(wbgt_t)) ;
if _N_>1 then sum=sum_wbgt+sum;
if _N_=6 then output;
run;
%mend;
%wbgt;
