BookmarkSubscribeRSS Feed
sasuser31
Calcite | Level 5

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!

2 REPLIES 2
ballardw
Super User

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
PROC Star

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.

  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2584 views
  • 1 like
  • 3 in conversation