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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.