topic Re: Create dummy variable dependent on time interval in SAS Data Management

Create dummy variable dependent on time interval

YoLohse — Sun, 24 Mar 2019 11:11:46 GMT

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

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

Re: Create dummy variable dependent on time interval

PaigeMiller — Sun, 24 Mar 2019 12:01:33 GMT

To find if a man finished 30 seconds or less after a woman, you can use the LAG function. Examples are given at: https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n0l66p5oqex1f2n1quuopdvtcjqb.htm&docsetVersion=9.4&locale=en

To find in a man finished 20 seconds or less before a woman, you will have to sort the data in reverse chronological order, and then use the LAG function again.

Re: Create dummy variable dependent on time interval

YoLohse — Sun, 24 Mar 2019 14:20:10 GMT

Hi Paige

Thanks for your reply, I don't think I know SAS well enough to do that without some help to the coding part.

My immediate idea would be to make a dummy 'Behind Woman' (BW) that is 1 if a man's time is between 1-30 seconds slower than a woman's time. Then a dummy 'in Front of Woman' (FW) that is 1 if a man's time between 1-20 seconds faster than a woman's time.

At last I would make a 'Close To Woman' (CTW) dummy that is 1 if either FW or BW = 1.

I don't know how to keep the latest woman observation in front/behind a man's time to use it for comparison when creating the Dummies. I see you have linked the LAG-function but i don't see how it can be used. I have linked the code that I have written now that I think could do the trick if i could get the 'WRunTime' (closes't woman in front/ behind's time) and 'WRunDate' (same but for date). Hope you can help!!

/* Sorterer til BW dummy */
proc sort 
data=DataAW; 
by RunDate RunTime; 
run;
/* Laver BW dummy */
data DataBW;
set DataAW;
If Gender= 1 and RunDate = WRundate and RunTime > WRunTime and RunTime < WRunTime+31 then BW = 1;
run;
/* Sorterer til FW dummy */
proc sort 
data=DataBW; 
by descending RunDate RunTime; 
run;
/* Laver FW dummy */
data DataFW;
set DataBW;
If Gender= 1 and RunDate = WRundate and RunTime < WRunTime and RunTime +21 > WRunTime then FW = 1; 
run;
/* Laver CTW dummy */
data DataCTW;
set DataFW;
If BW = 1 or FW = 1 then CTW = 1; 
run;

Re: Create dummy variable dependent on time interval

VDD — Sun, 24 Mar 2019 17:11:21 GMT

If Gender= 1 and RunDate = WRundate and RunTime > WRunTime and RunTime < WRunTime+31 then BW = 1;

where did wrundate and wruntime come from. you don't have those listed in the original data that you supplied.

if there was a data step that assigned those values it would really help.

Also the way you have the information being tested doesn't make logic since.

Why are you touching the data so many times.

Re: Create dummy variable dependent on time interval

YoLohse — Sun, 24 Mar 2019 17:30:07 GMT

WRunDate and WRunTime are the variables I need help creating, the W standing for Woman. I don't know if those are needed or if my IF-Statement will would work even if I got those variables.

The reason my code probably looks very suboptimal, incorrect and messy to you is because I have almost no experience at coding in SAS. I have only used VBA coding which is where my syntax is from when I do guesses of code like I did here.

Re: Create dummy variable dependent on time interval

Patrick — Sun, 24 Mar 2019 23:31:48 GMT

@YoLohse

SQL syntax is often quite useful for such use cases.


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