Divide list of names and keep data

[KellyJade]

Hi all,

I have data that looks like this

user	service	consultant
jack@gmail.com	Meeting	Jill, Jones, Jack
jane@gmail.com	Analysis	Jill
ana@gmail.com	Consult	Jones, Jack

I want data that looks like this

user	service	consultant
jack@gmail.com	Meeting	Jones
jack@gmail.com	Meeting	Jack
ana@gmail.com	Analysis	Jones
ana@gmail.com	Analysis	Jack

There are a few parts of this, 1) I want to remove Jill 2) I want to break out when one service has multiple consultants assigned, I want to break them up so that I can analyze each consultant's workload. I have tried to break it out by using this but it's not working

if consultant = 'Jill' then consultant = '';
   SUB = 'Jill';
   STR_LEN = length(consultants);
   SUB_LEN = length(SUB);
   POS = find(consultants,SUB,-STR_LEN);
   consultant = kupdate(STR,POS,SUB_LEN+1);                

I appreciate any insight you all have.

Thanks!!

[Astounding]

Is the variable actually named CONSULTANT or is it CONSULTANTS?

At any rate, here is an approach you can use.

data want;
   set have;
   length name $ 20;
   do i=1 by 1 until (name = ' ');
      name = scan(consultant, i, ',');
      if name not in (' ', 'Jill') then output;
   end;
   drop consultant i;
run;

[mkeintz]

I think you want to find all instances of a single consultant, and remove that consultants names from all other multi-name consultants.   And you also want to output one record for each surviving name in a multi-name consultants value:

data want (drop=_:);
  set have;
  if _n_=1 then do;
    declare hash singles(dataset:"have(where=(countw(consultants,',')=1))");
      singles.definekey('consultants');
      singles.definedata(all:'Y');
      singles.definedone();
  end;

  do _c=1 to countw(consultants);
    consultant=scan(consultants,_c); 
    if singles.check(key:consultant)^=0 then output;     
  end;
run;

Note I changed the initial variable name from consultant to consultants, and made a new variable in the WANT dataset (consultant).

This loads all the single-name instances of consultants into the memory-resident hash object singles.  It then loops though each consultant in consultants.  If that consultant is not in singles then output.