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?

Answer 1

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.

Answer 2

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

Answer 3

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: