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;
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;
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
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;
Anytime.
Please explain the logic that creates the output data set from the input data set.
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.