topic Re: summing monthly rows to arrive at fiancial year total in SAS Enterprise Guide

summing monthly rows to arrive at fiancial year total

Phil_from_PGA — Tue, 07 Nov 2017 03:59:22 GMT

Hi, I have data such as below and need to sum the sales by month to get a financial year total for 2013-14 and separately for 2014-15 where financial year for 2013-14 is July 2013 to June 2014 and for 2014-15 it is July 2014 to June 2015. That is, 2013-14 should sum to 342 and 2014-15 to 269. I also don't know how to turn the variable called monthly to a SAS month format in creating the example dataset below.

data have;

input monthly sales;

datalines;

JUL2013 9

AUG2013 12

SEP2013 23

OCT2013 0

NOV2013 1

DEC2013 22

JAN2014 45

FEB2014 5

MAR2014 77

APR2014 6

MAY2014 97

JUN2014 45

JUL2014 9

AUG2014 23

SEP2014 23

OCT2014 0

NOV2014 1

DEC2014 2

JAN2015 56

FEB2015 5

MAR2015 70

APR2015 68

MAY2015 7

JUN2015 5

;

run;

Re: summing monthly rows to arrive at fiancial year total

PGStats — Tue, 07 Nov 2017 04:40:58 GMT

Using proper SAS dates usually makes things simpler (once you understand how it all works)

data have;
input monthStr $ sales;
month = input(cats("01",monthStr),date9.);
format month monyy7.;
fy = year(intnx("year.7", month,0)); 
drop monthStr;
datalines;
JUL2013 9
AUG2013 12
SEP2013 23
OCT2013 0
NOV2013 1
DEC2013 22
JAN2014 45
FEB2014 5
MAR2014 77
APR2014 6
MAY2014 97
JUN2014 45
JUL2014 9
AUG2014 23
SEP2014 23
OCT2014 0
NOV2014 1
DEC2014 2
JAN2015 56
FEB2015 5
MAR2015 70
APR2015 68
MAY2015 7
JUN2015 5
;

proc sql;
create table fySales as
select fy, sum(sales) as fySales
from have
group by fy;

select * from fySales;

quit;

Re: summing monthly rows to arrive at fiancial year total

Phil_from_PGA — Tue, 14 Nov 2017 00:09:35 GMT

Thank you, very helpful.