Vertical column summation in sas

Question

I have the following piece of result, which i need to add. Seems like a simple request, but i have spent a few days already trying to find the solution to this problem.

Data have:

Measure   Jan_total   Feb_total
Startup      100         200
Switcher     300         500

Data want:

Measure   Jan_total   Feb_total
Startup      100         200
Switcher     300         500
Total        400         700

I want individually placed vertical sum results of each column under the respective column please.

Can someone help me arrive at the solution for this request, please?

Hi Sahil and welcome to StackOverflow. Consider explaining some background in your question. for example say it context (HTML CSS, React, Android, etc) so users can provide you meaningful answer. Also please add some code of your work in progress for better understanding of problem. — mhrabiee, Mar 02 '20 at 09:34
Does this answer your question? [Return dataset of column sums in SAS](https://stackoverflow.com/questions/41684015/return-dataset-of-column-sums-in-sas) — user667489, Mar 02 '20 at 10:12
If you got your perfect answer, please consider marking it as accepted. You can do it by pressing the check mark on the top left of the answer. Also voting up is an option. I'm mentioning this, because you are new to stackoverflow and since you showed your appreciation in the commentary, you might give the answerer his credit by giving him reputation score. Don't feel pressured to do so, since you always can wait for better answers or maybe it does not satisfy you enough. — mischva11, Mar 02 '20 at 12:39

score 3 · Accepted Answer · answered Mar 02 '20 at 09:52

To do this in data step code, you would do something like:

data want;
  set have end=end;       * Var 'end' will be true when we get to the end of 'have'.;
  jan_sum + jan_total;    * These 'sum statements' accumulate the totals from each observation.;
  feb_sum + feb_total;
  output;                 * Output each of the original obbservations.;
  if end then do;         * When we reach the end of the input...;
    measure = 'Total';    * ...update the value in Measure...;
    jan_total = jan_sum;  * ...move the accumulated totals to the original vars...;
    feb_total = feb_sum;
    output;               * ...and output them in an additional observation.
  end;
  drop jan_sum feb_sum;   * Get rid of the accumulator variables (this statement can go anywhere in the step).;
run;

You could do this many other ways. Assuming that you actually have columns for all the months, you might re-write the data step code to use arrays, or you might use PROC SUMMARY or PROC SQL to calculate the totals and add the resulting totals back using a much shorter data step, etc.

Thank you so much for the explanation and the detailed code. It gave me the final output that i needed. Appreciate it. — Sahil Khurana, Mar 02 '20 at 10:44
As author of the question, you have the option to mark an answer as the one you selected, giving 15 more reputation points to the author of the answer. — Dirk Horsten, Mar 05 '20 at 07:52
@Chris Think you can help me out a bit more? What if I have the same structure of data, but I need to calculate the sum for a range of variables, as was the case above? In the solution, you mentioned the individual variable names like jan_total, feb_total, etc. I have 14 months of such running data, which needs to be summed and the totals need to be shown below each category. Any thoughts? — Sahil Khurana, Mar 11 '20 at 11:50

score 2 · Answer 2 · answered Mar 02 '20 at 10:28

2

proc means noprint
    data = have;
    output out= want
    class measure;
    var Jan_total   Feb_total;
run;

answered Mar 02 '20 at 10:28

Dirk Horsten

3,753
4
20
37

score 0 · Answer 3 · answered Mar 02 '20 at 17:32

It depends on if this is for display or for a data set. It usually makes no sense to have a total in the data set and it's just used for reporting.

PROC PRINT has a SUM statement that will add the totals to the end of a report. PROC TABULATE also provides another mechanism for reporting like this.

example from here.

options obs=10 nobyline;
proc sort data=exprev;
   by sale_type;
run;
proc print data=exprev noobs label sumlabel
           n='Number of observations for the order type: '
           'Number of observations for the data set: ';
   var country order_date quantity price;
   label  sale_type='Sale Type'
          price='Total Retail Price* in USD'
          country='Country' order_date='Date' quantity='Quantity';
   sum price quantity;
   by sale_type;
   format price dollar7.2;
   title 'Retail and Quantity Totals for #byval(sale_type) Sales';
run;
options byline;

Results:

Vertical column summation in sas

3 Answers3