Help using Base SAS procedures

How to calculate sum across a row?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

How to calculate sum across a row?

Hi,

How to calculate sum across a row?

Like in below example I want to create two columns "Product1 Sale Sum" and "Product2 Sale Sum" which is the sum of Product1 and Product2 Sales in reporting months!!

I am using "Proc report" to create this report.

Any suggestion will be much appreciated.

Thanks in advance!!


Accepted Solutions
Solution
‎04-23-2015 11:03 AM
N/A
Posts: 1

Re: How to calculate sum across a row?

You use a compute block on the selected columns. See my paper on
Proc Report at www2.sas.com/proceedings/forum2008/079-2008.pdf.

Dave Lewandowski

data sales;
   input zone $ sub_zone $ sale_month $ product $ sales;
   datalines;
East East1 201501 Product1 100
East East2 201501 Product1 150
East East1 201502 Product1 150
East East2 201502 Product1 200
West West1 201501 Product1 100
West West2 201501 Product1 150
West West1 201502 Product1 150
West West2 201502 Product1 200
East East1 201501 Product2 200
East East2 201501 Product2 250
East East1 201502 Product2 300
East East2 201502 Product2 400
West West1 201501 Product2 200
West West2 201501 Product2 250
West West1 201502 Product2 300
West West2 201502 Product2 400
;

proc report data=sales missing headline;
  column zone sub_zone sale_month,product,sales prod1sum prod2sum;
  define zone / group width= 10 'Zone';
  define sub_zone / group width= 10 'Sub-Zone';
  define sale_month / across '';
  define product / across '';
  define sales / analysis sum format=4.0 '';
  define prod1sum / computed 'Product 1 Sum';
  define prod2sum / computed 'Product 2 Sum';
  break after zone / ol skip summarize suppress;
  rbreak after / dol summarize;
  compute prod1sum;
    prod1sum = sum(_C3_,_C5_);
  endcompute;
  compute prod2sum;
    prod2sum = sum(_C4_,_C6_);
  endcompute;
run;

                          201501      201502                       
                        Prod  Prod  Prod  Prod                     
                        uct1  uct2  uct1  uct2    Product    Product
Zone        Sub-Zone                                1 Sum      2 Sum
--------------------------------------------------------------------
East        East1        100   200   150   300        250        500
            East2        150   250   200   400        350        650
                        ----  ----  ----  ----  ---------  ---------
                         250   450   350   700        600       1150
                                                                   
West        West1        100   200   150   300        250        500
            West2        150   250   200   400        350        650
                        ----  ----  ----  ----  ---------  ---------
                         250   450   350   700        600       1150
                        ====  ====  ====  ====  =========  =========
                         500   900   700  1400       1200       2300
                       

View solution in original post


All Replies
Frequent Contributor
Posts: 98

Re: How to calculate sum across a row?

I am using following sample data and code to create my report:

proc report data=work.tempdata;

column Zone SubZone SaleMonth, (Product1Sale Product2Sale);

define zone / group;

define subzone/group;

define salemonth/across '';

define product1sale/analysis;

define product2sale/analysis;

break after zone / summarize;

compute after zone;

     zone='';   

     subzone="Sum:";

endcomp;

rbreak after / summarize;

compute after;

    zone='';

     subzone="Grand Sum:";

endcomp;

run;

quit;

Valued Guide
Posts: 860

Re: How to calculate sum across a row?

This might not be helpful at all but what I would do is get the sums that you want in a previous step:

data have;

input zone $ sub $ month sale1 sale2;

cards;

east east1 201501 100 200

east east2 201501 150 250

east east1 201502 150 300

east east2 201502 200 400

west west1 201501 100 200

west west2 201501 150 250

west west1 201502 150 300

west west2 201502 200 400

;

run;

proc sql;

create table want as

select *,sum(sale1) as sum_sale1,sum(sale2) as sum_sale2

from have

group by zone,sub;

From there you'll have all the information you need for your proc report.

Frequent Contributor
Posts: 98

Re: How to calculate sum across a row?

I know this is not a best way of achieving this but following code does what I want. Smiley Happy

But still I am looking for some way to achieve this directly from "Proc Report" Smiley Sad

/**** Calculate Sum in a seperate dataset*/

PROC SQL;

   CREATE TABLE WORK.QUERY_FOR_TEMPDATA AS

   SELECT t1.Zone,

          t1.SubZone,

            (sum(t1.Product1Sale)) AS Product1SaleSum,

            (sum(t1.Product2Sale)) AS Product2SaleSum

      FROM WORK.TEMPDATA t1

      GROUP BY t1.Zone,

               t1.SubZone;

QUIT;

/**** Join that dataset with original dataset*/

PROC SQL;

   CREATE TABLE WORK.TempDataMod AS

   SELECT t2.Zone,

          t2.SubZone,

          t2.SaleMonth,

          t2.Product1Sale,

          t2.Product2Sale,

          t1.Product1SaleSum,

          t1.Product2SaleSum

      FROM WORK.QUERY_FOR_TEMPDATA t1

           RIGHT JOIN WORK.TEMPDATA t2 ON (t1.Zone = t2.Zone) AND (t1.SubZone = t2.SubZone)

      ORDER BY t2.Zone,

               t2.SubZone,

               t2.SaleMonth;

QUIT;

/**** Use this new modified data set for reporting*/

proc report data=work.TempDataMod;

column Zone SubZone SaleMonth, (Product1Sale Product2Sale) Product1SaleSum Product2SaleSum ;

define zone / group;

define subzone/group;

define salemonth/across '';

define product1sale/analysis;

define product2sale/analysis;

define Product1SaleSum/group;

define Product2SaleSum/group;

break after zone / summarize;

compute after zone;

     zone='';   

     subzone="Sum:";

endcomp;

rbreak after / summarize;

compute after;

    zone='';

     subzone="Grand Sum:";

endcomp;

run;

quit;

Super User
Posts: 19,791

Re: How to calculate sum across a row?

Can you use proc tabulate instead of proc report?

Super User
Posts: 10,028

Re: How to calculate sum across a row?

Unfortunately , you have to use option OUT= to check the exact name of those columns:

proc report ....... out=xxxx  ;

Open xxxx and see what name they are , they may like  _Cxx_  ...

after that . build a compute column to sum them all.

compute xx;

xx=sum(_Cxx_,_Cxx_.....);   /*not sure if it would work too .      sum(of _: )   */

endcomp;

Xia Keshan

Solution
‎04-23-2015 11:03 AM
N/A
Posts: 1

Re: How to calculate sum across a row?

You use a compute block on the selected columns. See my paper on
Proc Report at www2.sas.com/proceedings/forum2008/079-2008.pdf.

Dave Lewandowski

data sales;
   input zone $ sub_zone $ sale_month $ product $ sales;
   datalines;
East East1 201501 Product1 100
East East2 201501 Product1 150
East East1 201502 Product1 150
East East2 201502 Product1 200
West West1 201501 Product1 100
West West2 201501 Product1 150
West West1 201502 Product1 150
West West2 201502 Product1 200
East East1 201501 Product2 200
East East2 201501 Product2 250
East East1 201502 Product2 300
East East2 201502 Product2 400
West West1 201501 Product2 200
West West2 201501 Product2 250
West West1 201502 Product2 300
West West2 201502 Product2 400
;

proc report data=sales missing headline;
  column zone sub_zone sale_month,product,sales prod1sum prod2sum;
  define zone / group width= 10 'Zone';
  define sub_zone / group width= 10 'Sub-Zone';
  define sale_month / across '';
  define product / across '';
  define sales / analysis sum format=4.0 '';
  define prod1sum / computed 'Product 1 Sum';
  define prod2sum / computed 'Product 2 Sum';
  break after zone / ol skip summarize suppress;
  rbreak after / dol summarize;
  compute prod1sum;
    prod1sum = sum(_C3_,_C5_);
  endcompute;
  compute prod2sum;
    prod2sum = sum(_C4_,_C6_);
  endcompute;
run;

                          201501      201502                       
                        Prod  Prod  Prod  Prod                     
                        uct1  uct2  uct1  uct2    Product    Product
Zone        Sub-Zone                                1 Sum      2 Sum
--------------------------------------------------------------------
East        East1        100   200   150   300        250        500
            East2        150   250   200   400        350        650
                        ----  ----  ----  ----  ---------  ---------
                         250   450   350   700        600       1150
                                                                   
West        West1        100   200   150   300        250        500
            West2        150   250   200   400        350        650
                        ----  ----  ----  ----  ---------  ---------
                         250   450   350   700        600       1150
                        ====  ====  ====  ====  =========  =========
                         500   900   700  1400       1200       2300
                       

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 956 views
  • 3 likes
  • 5 in conversation