BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
unifly
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Tourmaline | Level 20

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
Fluorite | Level 6
awesome ! thanks a lot
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2117 views
  • 3 likes
  • 4 in conversation