Contributor
Posts: 35

aggregate data on YOY

Hi ,

I want data like below way so i can plot trend on hierarchy level data.

could any one help.

Basically sum of percentage of group will should sum up to 100 in each year.

 Counts Percentage A 2012 20 22% 100 B 2012 30 33% C 2012 40 44% A 2013 50 38% 100 B 2013 20 15% C 2013 60 46% A 2014 80 36% 100 B 2014 120 55% C 2014 20 9%
Super User
Posts: 10,860

Re: aggregate data on YOY

```What is your output ?

data have;
input id \$ year count;
cards;
A	2012	20	22%	100
B	2012	30	33%
C	2012	40	44%
A	2013	50	38%	100
B	2013	20	15%
C	2013	60	46%
A	2014	80	36%	100
B	2014	120	55%
C	2014	20	9%
;
run;
proc sql;
select *,count/(select sum(count) from have where id=a.id) as per
from have as a;
quit;

```
Contributor
Posts: 35

Re: aggregate data on YOY

Thanks For response ,

However i am looking into SAS VA.

To make it more explanatory.

I am using Hierarchy in line /Time series chart Just take an example Have hierarchy as "Country->Seasons->Fruits"  on Y axis and Year on X axis.

So if we club the percentage for each year (may be just by seeing the graph) that should make 100% irrespective on which hierarchy (Category) we choosing and irrespective of their Hierarchy structure.

Hope i am clear.

Super User
Posts: 13,950

Re: aggregate data on YOY

This calculates percentages as requested:

``````data junk;
input group \$ year count;
datalines;
A 2012 20
B 2012 30
C 2012 40
A 2013 50
B 2013 20
C 2013 60
A 2014 80
B 2014 120
C 2014 20
;
run;

proc freq data=junk noprint;
by year;
tables group /  out=temp ;
weight count;
run;``````

If you have a dataset with just year and group sort it by Year and then run the proc freq without the WEIGHT statement. The output data set temp would have the count and the percent of records within each year.

Contributor
Posts: 35

Re: aggregate data on YOY

Thanks For response ,

However i am looking into SAS VA.

To make it more explanatory.

I am using Hierarchy in line /Time series chart Just take an example Have hierarchy as "Country->Seasons->Fruits"  on Y axis and Year on X axis.

So if we club the percentage for each year (may be just by seeing the graph) that should make 100% irrespective on which hierarchy (Category) we choosing and irrespective of their Hierarchy structure.

Discussion stats
• 4 replies
• 369 views
• 0 likes
• 3 in conversation