Contributor
Posts: 50

How to sum the variable according to identical other variables

[ Edited ]

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.

PROC Star
Posts: 500

Re: How to sum the variable according to identical other variables

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;``````
Contributor
Posts: 50

Re: How to sum the variable according to identical other variables

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.

PROC Star
Posts: 500

Re: How to sum the variable according to identical other variables

``````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;``````
Discussion stats
• 3 replies
• 128 views
• 0 likes
• 2 in conversation