topic Re: Sum Vertically in SAS Programming

Sum Vertically

luvscandy27 — Mon, 08 Jun 2020 17:10:00 GMT

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?

Re: Sum Vertically

ed_sas_member — Mon, 08 Jun 2020 17:17:06 GMT

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;

Re: Sum Vertically

mklangley — Mon, 08 Jun 2020 17:18:26 GMT

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;

Re: Sum Vertically

Reeza — Mon, 08 Jun 2020 17:25:29 GMT

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;

Re: Sum Vertically

novinosrin — Mon, 08 Jun 2020 17:33:36 GMT


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

Re: Sum Vertically

Reeza — Mon, 08 Jun 2020 17:51:06 GMT

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&docsetVersion=9.4&locale=en

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.

Re: Sum Vertically

novinosrin — Mon, 08 Jun 2020 17:53:00 GMT

Slick. Right on!!! Kudos!!!

Re: Sum Vertically

biopharma — Mon, 08 Jun 2020 18:33:43 GMT

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 ;

Re: Sum Vertically

luvscandy27 — Tue, 09 Jun 2020 08:48:57 GMT

Thank you everyone, for all of your help!