Hi
Typically there's a lead generation listing for marketing campaigns, let's say 10000 customers of marketing lead, each with different grouping.
Let's say 5000 customers with Group A, 2000 records with Group B and 3000 records with Group C
Have
custID group
ABC123 A
DEF456 A
GHI789 C
JKL101 A
MNO112 B
F000131 B
G000415 C
...
Each of the group has its own group of staffs to be equally assigned to for telemarketing
For example
staff group
John A
Paul A
Sartre A
Richard B
Olsen B
Lykke B
Chuck C
Norris C
Is there any function that allows me to equally assign the 10,000 customers to the list of staff by their group?
Want table is as below
custID group staff
ABC123 A Jean
DEF456 A Paul
GHI789 C Chuck
JKL101 A Sartre
MNO112 B Richard
F000131 B Olsen
G000415 C Norris
IJUIJ122 A Jean
YHU1232 A Paul
....
Thanks!
prospect_custID group
1 A
2 A
3 C
4 A
5 B
6 B
7 C
proc sort data=have;
by group;
run;
data want;
set have;
by group;
retain personnel_ID;
if first.group then personnel_id=1;
else personnel_id+1;
run;
HI @t30, Your question isn't clear to me and if you don't mind, please elaborate.
are your data in sas datasets?If yes,
How many?
what data is stored in those corresponding data-sets?a clear sample will help
what's the primary one and what are the look up ones?
And then explain your requirement with logic
May be there are very smart people who can comprehend from what you wrote, but I am afraid I am definitely not that kind. So if you may, please.
Do we have to assume the look up is exactly in sequence as your sample suggests? meaning
JKL101 A is the 3rd in A sequence in table HAVE and does it match 3rd in sequence in look up table Sartre A
In essence is it a sequence based match within groups?
Otherwise, it causes ambiguity as the question arises why can't ABC123 be he id of staff sartre?
Nope, it can be as random, as long as in the end, the 10,000 customers will be equally assigned to the staff based on their group.
But of course, if you follow by sequence, the customers will still be equally assign.
In a simpler example, let's say I have 10,000 customers, how do I equally assign these 10,000 customers to 10 staffs?
If there's a uniform distribution assignment, we can expect each staff to receive around 1000 customers each.
How do I do this in SAS data step? To complicate things, each customer and staff have their own group.
proc sort data=have;
by group;
run;
data want;
set have;
by group;
retain sort+id;
if first.group then sort_id=1;
else sort_id+1;
run;
dataset A;
personnel_ID group
A1557800 A
B1344223 A
C1233423 A
Dataset B;
personnel_ID group
A1235623 B
B1789234 B
C1909892 B
Dataset C;
personnel_ID group
A1234323 C
B8989132 C
C4234234 C
Data ABC;
Set A B C;
Run;
Proc sort data=abc;
By group;
Run;
Data abc2;
set abc;
by group;
retain sort_id;
if first.group then sort_id=1;
else sort_id +1;
run;
proc sort data=want;
by group sort_id;
run;
proc sort data=abc2;
by group sort_id;
run;
data all;
merge abc2 want;
by group sort_id;
run;
You could use an array to do random assignment within groups:
data Have;
input custID $ group $;
datalines;
ABC123 A
DEF456 A
GHI789 C
JKL101 A
MNO112 B
F000131 B
G000415 C
;
data staff;
input staff $ group $;
datalines;
John A
Paul A
Sartre A
Richard B
Olsen B
Lykke B
Chuck C
Norris C
;
data temp;
set have;
rnd = rand("uniform");
run;
proc sort data=temp; by group rnd; run;
proc sort data=staff; by group; run;
%let staffCount=100; /* Larger than any group staff count */
data want;
array s{&staffCount} $8 _temporary_;
do n = 1 by 1 until(last.group);
set staff; by group;
s{n} = staff;
end;
do i = 1 by 1 until(last.group);
set temp; by group;
staff = s{1 + mod(i, n)};
output;
end;
keep custID group staff;
run;
The algorithm relies on the groups being the same in both sets.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.