data test;
input id $ foodtype $ Nova $ Subnova $ kcals;
datalines;
001 butter 1 oils 10
001 fish 2 protein 15
001 banana 3 fruit 10
001 cherry 4 fruit 25
002 burger 2 frozen 5
002 pumpkin 2 other 4
002 carrot 3 veg 6
003 apple 1 fruit 100
003 tahini 4 spread 60
005 burger 2 frozen 13
005 ceral 3 grain 56
005 milk 1 prod 35
005 vinegar 4 Other 100
;
run;
proc sql;
create table temp as
select distinct 1 as a,id,input(nova,best.) as nova,cats('Total_',nova) as var length=80,sum(kcals) as value
from test
group by id,nova
union all
select distinct 2 ,id,input(nova,best.),cats('Percent_',nova) as var length=80,sum(kcals)/(select sum(kcals) from test where id=a.id ) as value
from test as a
group by id,nova
union all
select distinct 3 as a,id,999,'All_Total' as var length=80,sum(kcals) as value
from test
group by id
union all
select distinct 4 ,id,999,'All_Percent' as var length=80,1 as value
from test
group by id
union all
select distinct 5 ,'All',input(nova,best.),cats('Total_',nova) as var,sum(kcals) as value
from test
group by nova
union all
select distinct 6 ,'All',input(nova,best.),cats('Percent_',nova) as var, sum(kcals)/(select sum(kcals) from test ) as value
from test as b
group by nova
union all
select distinct 7 ,'All',9999,'All_Total' as var length=80,sum(kcals) as value
from test
union all
select distinct 8 ,'All',9999,'All_Percent' as var length=80,1 as value
from test
order by id,nova,a
;
quit;
proc transpose data=temp out=want(drop=_:);
by id;
id var;
var value;
format percent_: percent8.2;
run;
... View more
