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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.