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;
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;
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;
Thank you, very helpful.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.