Desktop productivity for business analysts and programmers

summing monthly rows to arrive at fiancial year total

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

summing monthly rows to arrive at fiancial year total

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;

 


Accepted Solutions
Solution
‎11-13-2017 07:09 PM
Esteemed Advisor
Posts: 5,626

Re: summing monthly rows to arrive at fiancial year total

Posted in reply to Phil_from_PGA

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;
PG

View solution in original post


All Replies
Solution
‎11-13-2017 07:09 PM
Esteemed Advisor
Posts: 5,626

Re: summing monthly rows to arrive at fiancial year total

Posted in reply to Phil_from_PGA

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;
PG
Occasional Contributor
Posts: 15

Re: summing monthly rows to arrive at fiancial year total

Thank you, very helpful.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 154 views
  • 0 likes
  • 2 in conversation