CODEWITHSUNDEEP
sas
NebulousReveal
NebulousReveal

Reputation: 572

Summing Values based on Grouping

Consider the following data set test :

 Drug  Quantity State Year
  A     
  B
  C
  .        .      .     .

How would I sum up the quantities of each drug grouped by state and year? Would it be something like:

 data test;
 by Drug State Year;
 Total = sum(Quantity)
 run;

Upvotes: 1

Views: 92

Answers (2)

Vasilij Nevlev
Vasilij Nevlev

Reputation: 1449

Mucio answer is good, but if you are after SAS SQL version, here it is: 

data test;
    input Drug $ Quantity State $ Year;
    datalines;
    A 10 NY 2013
    A 20 NY 2014
    B 110 NY 2013
    B 210 NY 2014   
    A 50 OH 2013
    A 60 OH 2014
    B 150 OH 2013
    B 260 OH 2014       
    A 22 NY 2014
    B 100 OH 2013
    ;
RUN;

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_TEST AS 
   SELECT t1.Drug, 
          t1.State, 
          t1.Year, 
          /* SUM_of_Quantity */
            (SUM(t1.Quantity)) AS SUM_of_Quantity
      FROM WORK.TEST t1
      GROUP BY t1.Drug,
               t1.State,
               t1.Year;
QUIT;

Result:

Result

Upvotes: 1

mucio
mucio

Reputation: 7119

You need something like this:

data test;
    input Drug $ Quantity State $ Year;
    datalines;
    A 10 NY 2013
    A 20 NY 2014
    B 110 NY 2013
    B 210 NY 2014   
    A 50 OH 2013
    A 60 OH 2014
    B 150 OH 2013
    B 260 OH 2014       
    A 22 NY 2014
    B 100 OH 2013
    ;
RUN;


proc means data= test SUM MAXDEC=0;
   class Drug State Year;   
   var Quantity;
RUN;

Upvotes: 1

Related Questions