Finding a match for one value in another column then pulling the value to the right of it.

[sasuser31]

I am pretty the solution to this is proc sql but I haven't touched that procedure very much so I need help.

I have recruitment data that has 6 columns: Particiapant ID, ID of 1st person recruited, ID of 2nd person recruited, ID of 3rd person recruited, ID of 4th person recruited, ID of 5th person recruited. I need a column that has the recuriter of the participant. I need to take the participant ID in the first column and search through the 5 columns of the IDs for the recruited among all of the observations. When I find a match, I need to pull the corresponding participant ID and throw it into a new variable for the original observation.

Anyone know of a relatively simple way to execute this?

Thanks!

[ballardw]

I think it will help to provide some example input data and the desired result. Best is to post example code as a datastep so we know exactly what things look like. 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 a SAS data set into data step code that can be pasted here as text in a code box opened with the {i} icon or attached as a text file.

[Astounding]

I would start by reformatting your data into just two columns, along these lines:

data want;

set have;

if Participant_ID > ' ';

Recruiter = Participant_ID;

if ID1 > ' ' then do;

   Participant_ID = ID1;

   output;

end;

if ID2 > ' ' then do;

   Participant_ID = ID2;

   output;

end;

...

if ID5 > ' ' then do;

   Participant_ID = ID5;

   output;

end;

keep Recruiter Participant_ID;

run;

This gives you a data set that you can compare with your current data.  match on the Participant_ID to determine the Recruiter (doesn't matter if  you use SQL or a MERGE in SAS ... whatever you are comfortable doing).

It is entirely possible that your data is not clean and that you have to work with it first.  For example, it is conceivable that more than one Participant claims to have recruited the same person.  You may need a process that selects who to use as the Recruiter when there is a conflict.