topic Re: Equally assign a list of values to another list of values by their respective Group in SAS Programming

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

t30 — Mon, 06 Aug 2018 03:30:54 GMT

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!

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

Jagadishkatam — Mon, 06 Aug 2018 02:02:37 GMT

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;

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

t30 — Mon, 06 Aug 2018 02:05:34 GMT

Hi @jaga,

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

Thanks

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

t30 — Mon, 06 Aug 2018 02:13:01 GMT

I've updated the table, unfortunately the personnel_id is not a running number as your assumption.

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

Jagadishkatam — Mon, 06 Aug 2018 02:13:02 GMT

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

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

novinosrin — Mon, 06 Aug 2018 02:16:47 GMT

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.

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

t30 — Mon, 06 Aug 2018 02:17:13 GMT

yes, basically I want to equally distribute the 10,000 records to a list of staff.

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

t30 — Mon, 06 Aug 2018 02:17:52 GMT

Hi sorry, let me rephrase it

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

novinosrin — Mon, 06 Aug 2018 02:33:18 GMT

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?

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

t30 — Mon, 06 Aug 2018 03:25:26 GMT

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.

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

Jagadishkatam — Mon, 06 Aug 2018 03:33:22 GMT

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;

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

PGStats — Mon, 06 Aug 2018 03:37:24 GMT

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;

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

t30 — Mon, 06 Aug 2018 04:57:19 GMT

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

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

t30 — Mon, 06 Aug 2018 04:59:10 GMT

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?

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

PGStats — Mon, 06 Aug 2018 05:14:12 GMT

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

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

hashman — Mon, 06 Aug 2018 05:30:56 GMT

@t30:

You can store the staff names along with group in a multidata hash table, then look it up by group for each record from HAVE. The search makes all staff names for this group available for selection. Then you just select one randomly.

data have ;                                      
  input custid:$8. group:$1. ;                   
  cards ;                                        
ABC123    A                                      
DEF456    A                                      
GHI789    C                                      
JKL101    A                                      
MNO112    B                                      
F000131   B                                      
G000415   C                                      
run ;                                            
                                                 
data names ;                                     
  input name:$8. group:$1. ;                     
  cards ;                                        
JOHN      A                                      
PAUL      A                                      
SARTRE    A                                      
RICHARD   B                                      
OLSEN     B                                      
LYKKE     B                                      
CHUCK     C                                      
NORRIS    C                                      
run ;                                            
                                                 
data want (keep = custid group name) ;           
  if _n_ =1 then do ;                            
    if 0 then set names ;                        
    call streaminit (7) ;                        
    dcl hash s (dataset:"names", multidata:"y") ;
    s.defineKey ("group") ;                      
    s.defineData ("name") ;                      
    s.defineDone () ;                            
  end ;                                          
  set have ;                                     
  do range = 0 by 1 while (s.do_over() = 0) ;    
  end ;                                          
  r = rand ("integer", range) ;                  
  do range = 1 by 1 while (s.do_over() = 0) ;    
    if r = range then output ;                   
  end ;                                          
run ;

FWIW

Paul D.

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

t30 — Mon, 06 Aug 2018 06:01:24 GMT

Yes correct, real life is a bit messy I guess 😄

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

t30 — Mon, 06 Aug 2018 06:20:24 GMT

Unknown method DO_OVER for DATASTEP.HASH

any SAS specific version for this? I'm using SAS 9.3, mostly only SAS Base

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

hashman — Mon, 06 Aug 2018 06:42:34 GMT

@t30:

The DO_OVER method was unveiled in 9.4. If you have 9.3, use the code below instead. It works in both 9.4 and 9.3. Also note the changes:

-- From rand("integer", range) to ceil(rand("uniform")*range) because the former isn't available in 9.3, either, so in 9.3 you have to do some extra arithmetic

-- From 0 to 1 in the FROM expression of the first DO loop because of the change from WHILE to UNTIL.

data want (keep = custid group name) ;                               
  if _n_ = 1 then do ;                                                
    if 0 then set names ;                                            
    call streaminit (7) ;                                            
    dcl hash s (dataset:"names", multidata:"y") ;                    
    s.defineKey ("group") ;                                          
    s.defineData ("name") ;                                          
    s.defineDone () ;                                                
  end ;                                                              
  set have ;                                                         
  if s.find() = 0 then do range = 1 by 1 until (s.find_next() ne 0) ;
  end ;                                                              
  r = rand ("integer", range) ;                                      
  if s.find() = 0 then do range = 1 by 1 until (s.find_next() ne 0) ;
    if r = range then output ;                                       
  end ;                                                              
run ;

With those changes, the output will be the same in 9.3 and 9.4 since it's not the algorithm itself that changes, just its version-specific implementation.

HTH

Paul D.

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

t30 — Mon, 06 Aug 2018 06:54:38 GMT

Thanks Paul, by the way, what do you intend to do with the rand function there? Normally rand only take the distribution function and has only 1 parametre