Sum Vertically

[luvscandy27]

I have the following data (excel file attached as well). 

FRUIT	MONTH	YEAR	TOTAL
ORANGE	1	2018	5
ORANGE	2	2018	10
ORANGE	3	2018	15
APPLE	1	2018	4
APPLE	2	2018	8
APPLE	3	2018	12

I would like to know how I can get a total for each month for oranges and apples combined. For example, for month 1 the total would be 9 for both oranges and apples. I would also like the table to look like below: 

FRUIT	MONTH	YEAR	TOTAL
ORANGE	1	2018	5
ORANGE	2	2018	10
ORANGE	3	2018	15
APPLE	1	2018	4
APPLE	2	2018	8
APPLE	3	2018	12
BOTH	1	2018	9
BOTH	2	2018	18
BOTH	3	2018	33

Could someone please assist me with this please? 

[ed_sas_member · Accepted solution]

Hi @luvscandy27 

Please try this:

proc sql;
	select * from fruit

	union all corr

	select 'BOTH' as fruit, month, year, sum(total) as total
	from fruit
	where fruit in ('ORANGE', 'APPLE')
	group by year, month;
run;

[mklangley]

I think TOTAL for BOTH when MONTH = 3 should be 27, not 33.

Try this:

proc sql;
    create table both as
    select 'BOTH' as fruit
          ,month
          ,year
          ,sum(total) as total
    from have
    group by month, year
    ;
quit;

data want;
    set have
        both;
run;

[Reeza]

Proc tabulate would be my choice.

proc tabulate data=have;
class fruit year month;
var total;
table (fruit all='Both')*Month*Year, Total*SUM = "Total"*f=comma.;
run;

[novinosrin]

data have;
input FRUIT $	MONTH 	YEAR 	TOTAL;
cards;
ORANGE	1	2018	5
ORANGE	2	2018	10
ORANGE	3	2018	15
APPLE	1	2018	4
APPLE	2	2018	8
APPLE	3	2018	12
;

data want;
 if _n_=1 then do;
  dcl hash H (ordered:'y') ;
  h.definekey  ("month","year") ;
  h.definedata ("month","year","total") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do until(z);
  set have(rename=(total=_total)) end=z;
  if h.find()=0 then TOTAL=sum(_total,total);
  else total=_total;
  h.replace();
  output;
 end;
 Fruit='Both';
 do while(hi.next()=0);
  _total=total;
  output;
 end;
 drop total;
 rename _total=total;
run;
proc print noobs;run;

FRUIT	MONTH	YEAR	total
ORANGE	1	2018	5
ORANGE	2	2018	10
ORANGE	3	2018	15
APPLE	1	2018	4
APPLE	2	2018	8
APPLE	3	2018	12
Both	1	2018	9
Both	2	2018	18
Both	3	2018	27

[Reeza]

proc means data=have;

class fruits year month;

types fruits*year*month year*month;

var total;

output out=want sum = total;

run;

You can also selectively use the TYPES statement to explicitly control the granularity of the data you report.

https://documentation.sas.com/?docsetId=proc&docsetTarget=p1ivm86f1sq3r1n13nykew2kdimp.htm&docsetVer...

You can also use the WAYS statement, or PROC REPORT. There's many ways to do this one 🙂

A customized format and PROC FREQ is also an option. One advantage to the method above, it's dynamic. Doesn't matter how many months or fruits you have and no need to know that information ahead of time.

[novinosrin]

Slick. Right on!!! Kudos!!!

[biopharma]

And just for fun another data step solution:

data want ;
   set have end = eof ;
   array m (12) _temporary_ ;
   m(month) + total ;
   output ;
   if eof then 
      do _n_ = 1 to 12 ;
         fruit = "BOTH" ;
         if not m(_n_) then continue ;
         month = _n_ ;
         total = m(_n_) ;
         output ;
      end ;
run ;

[luvscandy27]

Thank you everyone, for all of your help!