How to sum the variable according to identical other variables

[yanshuai]

Hello all.

I have data such as:

STORE     YEAR     SALE             PRODUCT#

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

First, how to sum the SALE (call it SALE1) with the same STORE, YEAR, and PRODUCT#? then SUM the SALE (call it SALE2) with the same STORE and YEAR, regardless of the PRODUCT#, and divide SALE1 by SALE2.

Second, how to sum all the SALE (call it SALE3) with the same YEAR and PRODUCT#, regardless of the STORE. then divide SALE1 by SALE3?

Thank you all very much!

I just don't know how to express condition "same" in SAS.

[kiranv_]

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;

[yanshuai]

   OBS store  year   SALE  PROD   SALE1  SALE2  SALE3  SALE1/SALE3   SALE1/SALE2
     1    001    2001    12      1234      22         46         44       0.478                    0.500
     2    001    2001    10      1234      22         46         44       0.478                    0.500
     3    002    2001    12      1234      22         46         44       0.478                    0.500
     4    002    2001    10      1234      22         46         44       0.478                    0.500
     5    002    2002    10      1234      20         40         40       0.500                    0.500
     6    001    2002    10      1234      20         40         40       0.500                    0.500
     7    001    2002    10      1234      20         40         40       0.500                    0.500
     8    002    2002    10      1234      20         40         40       0.500                    0.500
     9    002    2001    10      2234      24         46         48       0.522                    0.500
    10    002    2001    14      2234      24        46         48       0.522                    0.500
    11    001    2001    10      2234      24        46         48       0.522                    0.500
    12    001    2001    14      2234      24        46        48       0.522                     0.500
    13    002    2002    10      2234      20        40        40       0.500                     0.500
    14    001    2002    10      2234      20        40        40       0.500                     0.500
    15    001    2002    10      2234      20        40        40       0.500                     0.500
    16    002    2002    10      2234      20        40        40       0.500                     0.500

Thank you for the code. It works well.

I actually only care about SALE1/SALE3 and SALE1/SALE2. Those SALE SALE1 SALE2 SALE3 can be dropped.But PRODUCT# should be kept.

Then how can I drop those duplicates? Maybe there will be empty value if keep PRODUCT# but drop SALE_ ?

Thank you.

[kiranv_]

just add drop statement

proc sql ;
create table want( drop = columns you 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;