topic Re: How to sum the variable according to identical other variables in SAS Programming

How to sum the variable according to identical other variables

yanshuai — Thu, 22 Feb 2018 02:45:17 GMT

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 2234

002 2001 12 1234

002 2001 10 1234

002 2001 14 2234

002 2001 10 2234

002 2002 10 1234

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.

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

kiranv_ — Thu, 22 Feb 2018 05:10:17 GMT

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;

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

yanshuai — Wed, 28 Feb 2018 01:43:12 GMT

   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.

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

kiranv_ — Wed, 28 Feb 2018 04:00:32 GMT

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;