Create variable where sas loops through data

[YoLohse]

Hi

I'm new to sas but quite experienced in Excel, this dataset however is too big for Excel and i need to use SAS.

I have 4 variables from a series of 5k runs. RunnerID (each runner has a unique ID), RunDate (date of the run), Placement (in what place the runner finished) and Gender (0=woman 1=man).

I want to create a new variable that is =1 if a man finished directly behind a woman ie. RunDates for the two individuals are equal and the man's placement is = woman's placement+1. the variable should be 0 otherwise and should be empty for women.

I want SAS to look at the first man in the dataset and compare him with all the women that ran the same day to see if he finished behind a woman. After this, SAS should go do the same for the next man and so on and so on.

In excel i would do this with a loop, but i don't know how it is done in SAS

Below is a code snip of how I think it should look, and I have written in words what I think the If-statement should contain.

I need help to:

- Only loop through men and preferably only compare men to women who ran the same date for quicker processing

- How to refer to different observations in SAS when doing the If-statement

data AllData;
set AllData;
After_Woman = 1 if *man's RunDate = woman's RunDate and man's placement = woman's placement+1 else After_Woman = 0*

Hope you guys can help! 

[ballardw]

First comment. As newish user do not use the code construct like:

Data alldata;
    set alldata;
<other code>;
run;

Create a new data set. Any logic error in your code completely replaces the existing data, and with a large data set you may not detect some types of that behavior until way later in a process and not be able to tell when it happened.

Second, provide example data in the form of a data step to see what you have and then show what you want. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Showing exactly how the output is supposed to appear is very important.

This is especially important if you have dates or times as solutions that assume your dates and times are actually SAS date values won't work if you have character values or numbers such as 20190101 that you think are a date but that SAS would treat as an invalid date as it actually exceeds the current allowable range of dates.

[PGStats · Accepted solution]

Try this:

proc sort data=allData; by rundate placement; run;

data want;
set allData; by rundate;
retain prevGender;
if gender=1 then after_woman = prevGender = 0 and not first.rundate;
prevGender = gender;
drop prevGender;
run;

(untested)

[YoLohse]

Thanks PG i worked like a charm!

I have a follow up question. I also want a dummy that is 1 if a man finished 20 seconds before or 30 seconds after a woman, else 0. The 'RunTime' variable is in seconds by the way. Here I cant just look at the previous runner as multiple men could finish within a 30 second interval of the same woman, can you also help me with code for this variable?

data WORK.DATAWAW;
  infile datalines dsd truncover;
  input RunDate:MMDDYY10. RunnerID:BEST. Gender:$1. Age:BEST. RunTime:BEST. Placement:BEST. After_Woman:32.;
  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 0
10/22/2011 33415 1 44 1134 2 0
10/22/2011 196982 1 44 1164 3 0
10/22/2011 92330 1 24 1182 4 0
10/22/2011 33809 1 59 1188 5 0
;;;;