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?
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;
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;
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;
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;
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 |
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.
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.
Slick. Right on!!! Kudos!!!
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 ;
Thank you everyone, for all of your help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.