Dear friends,
How can I sum by ID numbers and arranging and merging by ID types? My dataset is:
ID_number | ID_type | paid_out |
1 | AA | 150 |
1 | BB | 200 |
2 | CC | 100 |
2 | CC | 50 |
2 | AA | 20 |
3 | BB | 10 |
4 | BB | 50 |
4 | AA | 60 |
5 | BB | 70 |
6 | CC | 90 |
7 | AA | 80 |
7 | CC | 70 |
7 | BB | 20 |
8 | BB | 100 |
8 | CC | 10 |
9 | AA | 10 |
9 | CC | 30 |
9 | BB | 30 |
10 | BB | 20 |
I like to have distinct ID_number and summing paid_out for each ID_number. The most important thing is arranging and merging the ID_type. I have three different ID_type: AA, BB and CC. I like to merge them to one cell for each ID_number. Moreover, I like to arrange them in this order: AA-BB-CC if I have all AA, BB or CC available (it's not sure). I like to have the following table:
ID_number | ID_type | paied |
1 | AA-BB | 350 |
2 | AA-CC | 170 |
3 | BB | 10 |
4 | AA-BB | 110 |
5 | BB | 70 |
6 | CC | 90 |
7 | AA-BB-CC | 170 |
8 | BB-CC | 110 |
9 | AA-BB-CC | 70 |
10 | BB | 20 |
data have;
infile datalines delimiter = " ";
input ID_number ID_type :$2. paid_out;
datalines;
1 AA 150
1 BB 200
2 CC 100
2 CC 50
2 AA 20
3 BB 10
4 BB 50
4 AA 60
5 BB 70
6 CC 90
7 AA 80
7 CC 70
7 BB 20
8 BB 100
8 CC 10
9 AA 10
9 CC 30
9 BB 30
10 BB 20
;
run;
proc sort data = have;
by id_number id_type;
run;
data want (keep = id_number id_type_combined total rename = (id_type_combined = id_type));
do until (last.id_number);
set have;
length id_type_combined $10.;
by id_number;
if first.id_number then total = paid_out;
else total + paid_out;
if first.id_number then id_type_combined = id_type;
else id_type_combined = catx("-", id_type_combined, id_type);
end;
run;
ID_number id_type total 1 AA-BB 350 2 AA-CC-CC 170 3 BB 10 4 AA-BB 110 5 BB 70 6 CC 90 7 AA-BB-CC 170 8 BB-CC 110 9 AA-BB-CC 70 10 BB 20
data have;
input ID_number ID_type $ paid_out;
datalines;
1 AA 150
1 BB 200
2 CC 100
2 CC 50
2 AA 20
3 BB 10
4 BB 50
4 AA 60
5 BB 70
6 CC 90
7 AA 80
7 CC 70
7 BB 20
8 BB 100
8 CC 10
9 AA 10
9 CC 30
9 BB 30
10 BB 20
;
data want(drop = i p);
set have(rename = (ID_type = i paid_out = p));
by ID_number;
length ID_type $ 200;
if first.ID_number then do;
paid_out = 0;
ID_type = '';
end;
ID_type = catx('-', ID_type, i);
paid_out + p;
if last.ID_number;
retain ID_type;
run;
Result:
ID_number ID_type paid_out 1 AA-BB 3 50 2 CC-CC-AA 170 3 BB 10 4 BB-AA 110 5 BB 70 6 CC 90 7 AA-CC-BB 170 8 BB-CC 110 9 AA-CC-BB 70 10 BB 20
data have;
infile datalines delimiter = " ";
input ID_number ID_type :$2. paid_out;
datalines;
1 AA 150
1 BB 200
2 CC 100
2 CC 50
2 AA 20
3 BB 10
4 BB 50
4 AA 60
5 BB 70
6 CC 90
7 AA 80
7 CC 70
7 BB 20
8 BB 100
8 CC 10
9 AA 10
9 CC 30
9 BB 30
10 BB 20
;
run;
proc sort data = have;
by id_number id_type;
run;
data want (keep = id_number id_type_combined total rename = (id_type_combined = id_type));
do until (last.id_number);
set have;
length id_type_combined $10.;
by id_number;
if first.id_number then total = paid_out;
else total + paid_out;
if first.id_number then id_type_combined = id_type;
else id_type_combined = catx("-", id_type_combined, id_type);
end;
run;
ID_number id_type total 1 AA-BB 350 2 AA-CC-CC 170 3 BB 10 4 AA-BB 110 5 BB 70 6 CC 90 7 AA-BB-CC 170 8 BB-CC 110 9 AA-BB-CC 70 10 BB 20
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.