group the values ​​of a variable

[unifly]

Hi everyone,

I have an issue. I would like to group the values of a variable.

For example, I have this :

id   bank  

1   1025  

1   4567  

1   1025  

1   3567

2   1025

2   4625

And I would like to have :

id   bank   other_bank

1   1025   4567; 3567

1   4567   1025;3567

1   1025   4567;3576

1   3567   1025;4567

2   1025   4625

2   4625   1025

I tried this but it's not enough what I want:

data orig;
    input id bank;
    cards;
1   1025  
1   4567  
1   1025  
1   3567
2   1025
2   4625
;
run;

proc sort data=orig;
    by id;
run;

data orig2;
   set orig; 
   by id;
   length other_bank $ 30;
   retain other_bank;

   if first.id then other_bank=bank;
   else other_bank=catx(';',other_bank,bank); 

run;

[PeterClemmensen]

data want(drop=b);
   dcl hash h (dataset : 'have(rename=bank=b)', multidata : 'Y');
   h.definekey('id');
   h.definedata('b');
   h.definedone();

   set have;
   length other_bank b $200;
   call missing(other_bank);

   do while (h.do_over() = 0);
      if bank ne b and indexw(other_bank, strip(b), ' ;') = 0 
         then other_bank = catx(';', other_bank, strip(b));
   end;
run;

Result:

id  bank  other_bank 
1   1025  4567;3567 
1   4567  1025;3567 
1   1025  4567;3567 
1   3567  1025;4567
2   1025  4625 
2   4625  1025 

[unifly]

thanks it's exactly what I want, but it doesn't work when I run it. Does your set "have" is exactly the same than my set "orig" ?

[unifly]

because I have the Following errors :
ERROR: Type mismatch for data variable b at ligne 46 colonne 4.
ERROR: Hash data set load failed at ligne 46 colonne 4.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

[PeterClemmensen · Accepted solution]

Sorry, did not realize your variables were numeric. This should do it. Let me know if it works..

data orig;
    input id bank;
    cards;
1   1025
1   4567
1   1025
1   3567
2   1025
2   4625
;
run;

data want(drop=b);
   if _N_ = 1 then do;
      dcl hash h (dataset : 'orig(rename=bank=b)', multidata : 'Y');
      h.definekey('id');
      h.definedata('b');
      h.definedone();
   end;

   set orig;
   length other_bank $200;
   call missing(other_bank);

   do while (h.do_over() = 0);
      if bank ne b and indexw(other_bank, strip(put(b, 8.)), ' ;') = 0 
         then other_bank = catx(';', other_bank, strip(put(b, 8.)));
   end;
run;

[unifly]

awesome ! thanks a lot

[PeterClemmensen]

Anytime.

[PaigeMiller]

Please explain the logic that creates the output data set from the input data set.

[Ksharp]

data orig;
    input id bank;
    cards;
1   1025  
1   4567  
1   1025  
1   3567
2   1025
2   4625
;
run;

proc sort data=orig;
    by id;
run;

data orig2;
do until(last.id);
   set orig; 
   by id;
   length all_bank $ 200;
   if not findw(all_bank,strip(bank),' ') then all_bank=catx(' ',all_bank,bank);
end;
do until(last.id);
   set orig; 
   by id;
   other_bank=translate(compbl(strip(prxchange(cats('s/\b',bank,'\b//'),-1,all_bank))),';',' ');
   output;
end;
drop all_bank;
run;