aggregate data on YOY

[Dd07]

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.

Please suggest.

		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%

[Ksharp]

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;

[Dd07]

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.

[ballardw]

You do not describe what you start with.

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.

[Dd07]

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.