BookmarkSubscribeRSS Feed
t30
Fluorite | Level 6 t30
Fluorite | Level 6

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!

24 REPLIES 24
Jagadishkatam
Amethyst | Level 16
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;
Thanks,
Jag
t30
Fluorite | Level 6 t30
Fluorite | Level 6
Hi @jaga,

The last data step, how does you refer the personnel_ID as it's from another table?

Thanks
Jagadishkatam
Amethyst | Level 16
you mean to say the personnel_ID is coming from another dataset. I did not understand your question, in the main dataset we have prospect_custID, which has difference numbers for each group. Could you please give more details
Thanks,
Jag
t30
Fluorite | Level 6 t30
Fluorite | Level 6
yes, basically I want to equally distribute the 10,000 records to a list of staff.
novinosrin
Tourmaline | Level 20

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.

 

t30
Fluorite | Level 6 t30
Fluorite | Level 6
Hi sorry, let me rephrase it
novinosrin
Tourmaline | Level 20

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?

t30
Fluorite | Level 6 t30
Fluorite | Level 6

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.

 

t30
Fluorite | Level 6 t30
Fluorite | Level 6
I've updated the table, unfortunately the personnel_id is not a running number as your assumption.
Jagadishkatam
Amethyst | Level 16
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;






Thanks,
Jag
t30
Fluorite | Level 6 t30
Fluorite | Level 6
Hi Jag, I think the latter part doesn't work. PG reply works for his 2 test tables though, at least you can get the idea from there
PGStats
Opal | Level 21

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;
PG
t30
Fluorite | Level 6 t30
Fluorite | Level 6
Hi PG, any reason why if some groups don't exist in the either table, the array assignment above will no longer work?

For example in my staff table, there are some staffs with no group, so the default value is blank as in ''.
Some staffs have Group Z but Group Z is not part of the customer table.

Your array assignment works only if the groups are present in both tables. Is there any way to avoid such assumption?
PGStats
Opal | Level 21

The algorithm relies on the groups being the same in both sets.

PG

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!

How to Concatenate Values

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.

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
  • 24 replies
  • 1298 views
  • 0 likes
  • 7 in conversation