Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

aggregate data on YOY

Reply
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.

Please suggest.

  CountsPercentage 
A20122022%100
B20123033%
C20124044%
A20135038%100
B20132015%
C20136046%
A20148036%100
B201412055%
C2014209%
Super User
Posts: 9,858

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: 11,105

Re: aggregate data on YOY

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.

 

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.

 

Ask a Question
Discussion stats
  • 4 replies
  • 234 views
  • 0 likes
  • 3 in conversation