How about ?
Data have;
infile datalines dlm= " ";
input ID :$20. Class :$2. Year Group Total;
datalines;
A001003 X 2012 5 10
A001003 X 2012 5 10
A001003 X 2012 5 10
A001002 X 2012 5 10
A001004 X 2012 5 10
A001005 X 2012 5 10
A001005 X 2012 5 10
A001004 X 2012 5 10
A001001 X 2012 5 10
A001001 X 2012 5 10
A002007 X 2013 4 8
A002005 X 2013 4 8
A002007 X 2013 4 8
A002003 X 2013 4 8
A002003 X 2013 4 8
A002001 X 2013 4 8
A002007 X 2013 4 8
A002001 X 2013 4 8
A002004 Y 2013 2 3
A002004 Y 2013 2 3
A002002 Y 2013 2 3
A002006 Z 2013 1 2
A002006 Z 2013 1 2
A003003 X 2014 3 5
A003003 X 2014 3 5
A003004 X 2014 3 5
A003001 X 2014 3 5
A003007 X 2014 3 5
A003002 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003008 Y 2014 3 7
A003005 Y 2014 3 7
A003005 Y 2014 3 7
A003002 Y 2014 3 7
A003006 Z 2014 1 1
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004007 X 2015 6 12
A004001 X 2015 6 12
A004004 X 2015 6 12
A004011 X 2015 6 12
A004002 X 2015 6 12
A004009 X 2015 6 12
A004001 X 2015 6 12
A004007 X 2015 6 12
A004006 X 2015 6 12
A004010 Y 2015 3 5
A004010 Y 2015 3 5
A004008 Y 2015 3 5
A004008 Y 2015 3 5
A004003 Y 2015 3 5
A004005 Z 2015 1 3
A004005 Z 2015 1 3
A004005 Z 2015 1 3
;
run;
proc sql;
create table temp as
select 1 as id,year,'group' as name,max(group) as value
from have
group by year
union
select 2 as id,year,'total' as name,count(*) as value
from have
group by year
union
select 3 as id,year,catx(' ','% Class',class) as name,
int(100*count(*)/(select count(*) from have where year=a.year)) as value
from have as a
group by year,class
order by id,name
;
quit;
proc transpose data=temp out=want(drop=_name_);
by id name;
id year;
var value;
run;
... View more