topic Re: sum by ID numbers and arranging and merging by ID types in SAS Programming

sum by ID numbers and arranging and merging by ID types

Farshid22 — Thu, 02 Mar 2023 13:13:44 GMT

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

Re: sum by ID numbers and arranging and merging by ID types

PeterClemmensen — Thu, 02 Mar 2023 13:37:49 GMT

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

Re: sum by ID numbers and arranging and merging by ID types

maguiremq — Thu, 02 Mar 2023 13:39:27 GMT

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

Re: sum by ID numbers and arranging and merging by ID types

Farshid22 — Thu, 02 Mar 2023 21:24:16 GMT

Thank you very much my friend