Calculation of rates and aggregation

[aguilar_john]

Hello everyone,

I need to create a table which includes some calculated rates. The table I have looks like this:

Date	ID	ID_2	Amount
2019/01	A	1	100
2019/01	A	0	300
2019/01	B	1	200
2019/01	C	0	400
2019/01	C	1	500
2019/02	A	0	600
2019/02	A	1	700
2019/02	B	1	800
2019/02	C	1	900

My desired table should look like this:

Date	ID	ID_2	Total
2019/01	A	13%	27%
2019/01	B	25%	13%
2019/01	C	63%	60%
2019/02	A	29%	43%
2019/02	B	33%	27%
2019/02	C	38%	30%

I need to group per Date and ID and I need the rate for ID_2/Total ID_2 and ID_2/Total. At the end I would need the percentage of all IDs for each ID_2 and the share of ID_2 in relation to the overall sum of ID_2.

Meaning --> Sum 2019/01 for ID_2=1 --> 800, since for 2019/01 for ID=A there is an amount of 100, I have 100/800=13%

For the values in Total of my desired table à Sum 2019/01 for ID_2=1 --> 1500, since for 2019/01 for ID=A there is 100+300, I have 400/1500=27%.

I would need the syntax which gives me a SAS table as a result, therefore proc tabulate is not an option for me.

Thank you very much for the help.

[Kurt_Bremser · Accepted solution]

You would help us greatly by posting data in readily usable form, like this:

data have;
format date yymms7.;
input _Date :$7. ID $ ID_2 Amount;
date = input(compress(_date,'/'),yymmn6.);
drop _date;
datalines;
2019/01 A 1 100
2019/01 A 0 300
2019/01 B 1 200
2019/01 C 0 400
2019/01 C 1 500
2019/02 A 0 600
2019/02 A 1 700
2019/02 B 1 800
2019/02 C 1 900
;

Since you did not do this, I had to make some assumptions about data types and formats.

Based on the above data, this produces your desired result:

proc sort data=have;
by date id;
run;

data want;
do until (last.date);
  set have;
  by date;
  sum_id2 = sum(sum_id2,ifn(id_2,amount,0));
  sum_total = sum(sum_total,amount);
end;
do until (last.date);
  sum_id = 0;
  sum_id_2 = 0;
  do until (last.id);
    set have;
    by date id;
    sum_id_2 = sum(sum_id_2,ifn(id_2,amount,0));
    sum_id = sum(sum_id,amount);
  end;
  id_2 = sum_id_2 / sum_id2;
  total = sum_id / sum_total;
  output;
end;
format id_2 total percent5.;
keep date id id_2 total;
run;

[aguilar_john]

Thank you very much for your help. Well noticed regarding data input, will do so next time.