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.
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
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.
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;
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!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.