Your sample output isn't quite consistent with respect to the order in which the names are collected. To wit, for the record with points=80, you first collect the items with pdt_cde=201 from that point down (h, j) and then - from that point up (d, a). Yet for the record with points=90, you have nothing to find downwards for pdt_cde=202 but then proceed to collect the items not from that point up (g, b) but from the top of the table down (b, g). So, there're 3 possibilities I see:
1. You don't care in which order the agent names are collected for the same pdt_cde.
2. You want to collect from the current record down, then from the top of the table down.
3. You want to collect from the current record down, then from this record up.
However, whatever the case, first you need to find out how many agent name variables you need. It's easy:
data have ;
input pdt_cde agent_name $ points ;
cards ;
201 a 10
202 b 20
203 c 30
201 d 40
204 e 50
203 f 60
202 g 70
201 h 80
202 i 90
201 j 100
;
run ;
proc sql noprint ;
select cats (max(q)) into: nv
from (select pdt_cde, count (pdt_cde) as q from have group 1)
;
quit ; Now let's look at the choice #1 above (you don't care about the collection order) since it's the easiest to code:
data want (keep = points name:) ;
if _n_ = 1 then do ;
dcl hash h (dataset:"have", multidata:"y") ;
h.definekey ("pdt_cde") ;
h.definedata ("pdt_cde", "agent_name") ;
h.definedone () ;
end ;
array name (j) $ name1 - name&nv ;
set have ;
call missing (of name[*]) ;
do j = 1 by 1 while (h.do_over() = 0) ;
name = agent_name ;
end ;
run ; The case #2 is more involved since now we have to traverse each same-key hash item group twice, though thankfully in the same direction. This appears to generate the output closest to what you've shown.
data want (keep = points name:) ;
dcl hash h (dataset:"have", multidata:"y") ;
h.definekey ("pdt_cde") ;
h.definedata ("pdt_cde", "agent_name") ;
h.definedone () ;
h.output (dataset:"hash") ;
array name (j) $ name1 - name&nv ;
do until (z) ;
set have (rename = (agent_name = an)) end = z ;
call missing (of name[*]) ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if an = agent_name then leave ;
end ;
j = 1 ;
name = agent_name ;
h.has_next (result:R) ;
do while (R ne 0) ;
h.find_next() ;
j + 1 ;
name = agent_name ;
h.has_next (result:R) ;
end ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if an = agent_name then leave ;
j + 1 ;
name = agent_name ;
end ;
output ;
end ;
stop ;
set have (keep = agent_name) ;
run ; Finally, the case #3 involves still more hash table traversing gymnastics since we now need to traverse each same-key hash item group both down and up. (But I do admit that it lends itself to a bit of creative hash object programming.)
data want (keep = points name:) ;
dcl hash h (dataset:"have", multidata:"y") ;
h.definekey ("pdt_cde") ;
h.definedata ("pdt_cde", "agent_name") ;
h.definedone () ;
h.output (dataset:"hash") ;
array name (j) $ name1 - name&nv ;
do until (z) ;
set have (rename = (agent_name = an)) end = z ;
call missing (of name[*]) ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if an = agent_name then leave ;
end ;
j = 1 ;
name = agent_name ;
h.has_next (result:R) ;
do while (R ne 0) ;
h.find_next() ;
j + 1 ;
name = agent_name ;
h.has_next (result:R) ;
end ;
if h.find() = 0 then do until (h.find_next() ne 0) ;
if an = agent_name then leave ;
end ;
h.has_prev (result:R) ;
do while (R ne 0) ;
h.find_prev() ;
j + 1 ;
name = agent_name ;
h.has_prev (result:R) ;
end ;
output ;
end ;
stop ;
set have (keep = agent_name) ;
run ;
HTH
Paul D.
p.s. Quite a few interesting variations and examples on this kind of theme are found in the SAS Press just published (penned together with Don Henderson):
https://support.sas.com/en/books/authors/paul-dorfman.html
