DATA Step, Macro, Functions and more

Equally assign a list of values to another list of values by their respective Group

Reply
Contributor t30
Contributor
Posts: 41

Equally assign a list of values to another list of values by their respective Group

[ Edited ]

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!

Trusted Advisor
Posts: 1,163

Re: Mapping a list of values to another list of values

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
Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

Posted in reply to Jagadishkatam
Hi @jaga,

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

Thanks
Trusted Advisor
Posts: 1,163

Re: Mapping a list of values to another list of values

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
Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

Posted in reply to Jagadishkatam
yes, basically I want to equally distribute the 10,000 records to a list of staff.
Super User
Posts: 2,050

Re: Mapping a list of values to another list of values

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.

 

Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

Posted in reply to novinosrin
Hi sorry, let me rephrase it
Super User
Posts: 2,050

Re: Mapping a list of values to another list of values

[ Edited ]

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?

Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

[ Edited ]
Posted in reply to novinosrin

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.

 

Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

Posted in reply to Jagadishkatam
I've updated the table, unfortunately the personnel_id is not a running number as your assumption.
Trusted Advisor
Posts: 1,163

Re: Mapping a list of values to another list of values

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
Contributor t30
Contributor
Posts: 41

Re: Mapping a list of values to another list of values

Posted in reply to Jagadishkatam
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
Esteemed Advisor
Posts: 5,624

Re: Equally assign a list of values to another list of values by their respective Group

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
Contributor t30
Contributor
Posts: 41

Re: Equally assign a list of values to another list of values by their respective Group

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?
Esteemed Advisor
Posts: 5,624

Re: Equally assign a list of values to another list of values by their respective Group

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

PG
Ask a Question
Discussion stats
  • 24 replies
  • 155 views
  • 0 likes
  • 7 in conversation