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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.