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

[t30]

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

[hashman]

@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.  

[t30]

Unknown method DO_OVER for DATASTEP.HASH

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

[hashman]

@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. 

[t30]

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

[t30]

Ignore this as you've explained it in the intro, thanks!

[hashman]

in 9.4, rand ("integer", begin, end) returns an integer from the uniform distribution in the range from integer begin to integer end. If begin=1, it can be omitted since begin=1 is assumed by default. Comes in quite handy because to do the same in 9.3, you have to code:

begin - 1 + ceil (rand ("uniform") * (end - begin + 1))

Produces the same result, but agree that coding merely:  

rand ("integer", begin, end)

is way simpler. Get 9.4, amigo ;).

HTH

Paul D.

[t30]

Yeah, actually 9.4 is available.

However it's not well configured to link across other legacy SAS tables stored in different shared paths.

So we're stuck using 9.3 for the time being. Thanks!

[ballardw]

---

@t30 wrote:
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

---

You may want to double check the syntax. Several distributions have no parameters: Cauchy, Exponential, Lognormal, Uniform. Others have more, Binomial, F, Hypergeometric, Negbinomial, Normal (none or 2 optional), Weibul have 2 and Table can have a very large number of parameters (never used more than 20 though)

[Ksharp]

If I understood what you mean .

data Have;
input custID  $     group $;
cards;
ABC123                    A
DEF456                    A
GHI789                     C
JKL101                      A
MNO112                   B
F000131                   B
G000415                   C
IJUIJ122                    A 
YHU1232                   A 
;
run;

data name;
input  staff   $    group $;
cards;
John                          A
Paul                          A
Sartre                        A
Richard                     B
Olsen                        B
Lykke                        B
Chuck                        C
Norris                        C
;
run;


data name;
 set name;
 by group;
 if first.group then k=0;
 k+1;
run;
proc summary data=name;
by group;
var k;
output out=mod(drop=_:) max=max;
run;

data want;
 if _n_=1 then do;
  if 0 then set mod;
  declare hash mod(dataset:'mod');
  mod.definekey('group');
  mod.definedata('max');
  mod.definedone();
  
  if 0 then set name;
  declare hash name(dataset:'name');
  name.definekey('group','k');
  name.definedata('staff');
  name.definedone();
  
  declare hash counter();
  counter.definekey('group');
  counter.definedata('n');
  counter.definedone();
 
 end;
set have;
if counter.find()=0 then do;n=n+1;counter.replace();end;
 else do;n=1;counter.add();end;
 
 call missing(max);
 rc=mod.find();
 k=ifn(mod(n,max)=0,max,mod(n,max));
 call missing(staff);
 rc=name.find();
 
 drop rc max k n;
run;

proc print noobs;run;