BookmarkSubscribeRSS Feed
Dd07
Fluorite | Level 6

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%
4 REPLIES 4
Ksharp
Super User
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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

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