topic Dummy variable dependent on time variable that doesn't include current observation in SAS Data Management

Dummy variable dependent on time variable that doesn't include current observation

YoLohse — Wed, 27 Mar 2019 15:00:36 GMT

I have a data set of men (gender =1) and women (gender=0) running 5k runs. I want a dummy that is 1 if a man finished 10 seconds before or 10 seconds after another man, else 0. For women the dummy should just be empty. The 'RunTime' variable is in seconds.

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 looks like this. The problem with the code is that is is just comparing the same guys time to his own resulting in the dummy being 1 all the time. Can someone show me how to change the code to get my wanted result?

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 might be able to bring!

Re: Dummy variable dependent on time variable that doesn't include current observation

novinosrin — Wed, 27 Mar 2019 15:26:41 GMT

Hi @YoLohse Can you please post the wanted result for your sample along with explanation of the logic. I can't see the wanted result

Re: Dummy variable dependent on time variable that doesn't include current observation

Patrick — Thu, 28 Mar 2019 06:19:05 GMT

@YoLohse

You simply need to exclude the same rows in the lookup.

Also using information you've provided here https://communities.sas.com/t5/SAS-Data-Management/Create-dummy-variable-dependent-on-time-interval/m-p/545599#M16794 column RunnerID should allow for this.

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.RunnerID ne o.RunnerID
                              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;

Re: Dummy variable dependent on time variable that doesn't include current observation

YoLohse — Sun, 31 Mar 2019 15:42:14 GMT

Thank you very much! Can you also alter the code to make a count variable, that counts the number of people finishing withing plus/minus 10 seconds of a runner?