topic Re: group the values of a variable in SAS Programming

group the values of a variable

unifly — Mon, 23 Nov 2020 13:33:56 GMT

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;

Re: group the values of a variable

PeterClemmensen — Mon, 23 Nov 2020 14:21:04 GMT

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

Re: group the values of a variable

PaigeMiller — Mon, 23 Nov 2020 14:11:43 GMT

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

Re: group the values of a variable

unifly — Mon, 23 Nov 2020 14:30:56 GMT

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" ?

Re: group the values of a variable

unifly — Mon, 23 Nov 2020 14:39:01 GMT

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.

Re: group the values of a variable

PeterClemmensen — Mon, 23 Nov 2020 15:10:00 GMT

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;

Re: group the values of a variable

unifly — Mon, 23 Nov 2020 15:29:30 GMT

awesome ! thanks a lot

Re: group the values of a variable

PeterClemmensen — Mon, 23 Nov 2020 16:46:04 GMT

Anytime.

Re: group the values of a variable

Ksharp — Tue, 24 Nov 2020 13:32:08 GMT

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;

topic Re: group the values ​​of a variable in SAS Programming

group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

Re: group the values ​​of a variable

topic Re: group the values of a variable in SAS Programming

group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable

Re: group the values of a variable