All you to do is group by at levels you want and then join back to main table. One way to achieve this is
data have ;
input
@ 1 STORE $3. @15 YEAR @26 SALE @45 PRODUCT;
datalines;
001 2001 12 1234
001 2001 10 1234
001 2001 14 2234
001 2001 10 2234
001 2002 10 1234
001 2002 10 1234
001 2002 10 2234
001 2002 10 2234
002 2001 12 1234
002 2001 10 1234
002 2001 14 2234
002 2001 10 2234
002 2002 10 1234
002 2002 10 1234
002 2002 10 2234
002 2002 10 2234
;
run;
proc sql ;
create table want as
select a.*, sale1, sale2, sale3,
sale1/sale2 as sale1bysale2 format = comma5.3,
sale1/sale3 as sale1bysale3 format = comma5.3
from
(select store, year, sale, product
from have ) a
full join
(select store, year, product, sum(sale) as sale1
from have
group by 1,2,3) b
on a.store =b.store
and a.year =b.year
and a.product =b.product
full join
(select store, year, sum(sale) as sale2
from have
group by 1,2) c
on a.store =c.store
and a.year =c.year
full join
(select year, product, sum(sale) as sale3
from have
group by 1,2) d
on a.product =d.product
and a.year =d.year;
... View more
![SASGF 2020 [Virtual] Attendee](/html/@A53A708409112314CE528E074D4C8C1F/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
