BookmarkSubscribeRSS Feed
YoLohse
Fluorite | Level 6

Hi

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-do...

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

2 REPLIES 2
Riteshdell
Quartz | Level 8

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.

Patrick
Opal | Level 21
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 595 views
  • 0 likes
  • 3 in conversation