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
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: