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!!
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: