Create varible that count number of runners in interval

YoLohse — Sun, 31 Mar 2019 20:50:07 GMT

I have a data set of men and women running 5k runs. I want a variable that counts how many other runners, that finished withing a ten second interval of the observation (+- 10 seconds). The 'RunTime' variable is in seconds.

I think the code i have been provided from previous questions about the same data set could be very usefull for this, but as I am not very experienced in SAS i don't know how to alter it to create this variable.

https://communities.sas.com/t5/SAS-Data-Management/Dummy-variable-dependent-on-time-variable-that-doesn-t-include/m-p/546793#M16823

My data looks like this:

data WORK.ALLDATA;
  infile datalines dsd truncover;
  input RunDate:MMDDYY10. RunnerID:BEST. Gender:$1. Age:BEST. RunTime:BEST. Placement:BEST.;
  format RunDate MMDDYY10. RunnerID BEST. Age BEST. RunTime BEST. Placement BEST.;
  label RunDate="RunDate" RunnerID="RunnerID" Gender="Gender" Age="Age" RunTime="RunTime" Placement="Placement";
datalines;
10/22/2011 198111 1 39 1102 1
10/22/2011 33415 1 44 1134 2
10/22/2011 196982 1 44 1164 3
10/22/2011 92330 1 24 1182 4
10/22/2011 33809 1 59 1188 5
10/22/2011 79259 1 54 1190 6
10/22/2011 60226 1 59 1210 7
10/22/2011 32065 1 39 1224 8
10/22/2011 134140 1 54 1236 9
10/22/2011 37522 1 44 1247 10
10/22/2011 173019 1 49 1251 11
10/22/2011 186056 0 54 1257 12
10/22/2011 184284 1 34 1275 13
10/22/2011 43517 1 44 1277 14
10/22/2011 39169 1 39 1287 15
10/22/2011 90659 1 39 1346 16
10/22/2011 196665 1 24 1379 17
10/22/2011 36277 1 44 1392 18
10/22/2011 31453 1 49 1394 19
10/22/2011 89064 1 39 1399 20
;;;;

The code I have been given earlier looks like this. I guess the gender statements should be removed and the variable be set to some kind of count function.

proc sql;
create table DataCTM as
    select 
      *,
      case
        when gender='0' then '.'
        when gender='1' and (select count(*) 
                              from WORK.ALLDATA I 
                              where i.gender='1' 
                              and i.RunDate=o.RunDate 
                              and o.RunTime+10>=i.RunTime and o.RunTime-10<=i.RunTime
                              ) > 0 
                        then '1'
        else '0'
        end
      as CTM
    from WORK.DataCTW O
    ;
quit;

Thanks in advance for any help you guys may be able to provide

Re: Create varible that count number of runners in interval

Riteshdell — Mon, 01 Apr 2019 06:50:33 GMT

I partially understood your question, based on your input table, could you please provide expected output here with new variable, I will try to generate code based on your output.

Re: Create varible that count number of runners in interval

Patrick — Mon, 01 Apr 2019 08:16:10 GMT

proc sql;
  create table DataCTM as
    select distinct
      l.*,
      case
        when missing(r.runnerid) then 0
        else count(*) 
        end   
        as cnt
    from WORK.ALLDATA l left join WORK.ALLDATA r
      on 
        l.RunDate=r.RunDate
        and l.RunnerID ne r.RunnerID
        and l.RunTime between r.RunTime+10 and r.RunTime-10
    group by l.RunDate, l.RunnerID
    order by l.RunDate, l.RunTime
    ;
quit;

topic Re: Create varible that count number of runners in interval in SAS Data Management

Create varible that count number of runners in interval

Re: Create varible that count number of runners in interval

Re: Create varible that count number of runners in interval