I have a date field called "Period" and want to return Quarter based on Australian Financial Year (1st July yyyy to 30 June yyyy) and also quarter for the Calendar Year.
Period is derived using the below code
%let crundate= 170428; Period = INPUT(PUT(&crundate ,8.),YYMMDD8.); format Period monyy7.;
Examples are below
Date Cal_Yr_Qtr Fin_Yr_Qtr
01/01/2016 to 30/03/2016 1st Qtr 2016 3rd Qtr 2015/2016
01/04/2016 to 30/06/2016 2nd Qtr 2016 4th Qtr 2015/2016
01/07/2016 to 30/09/2016 3rd Qtr 2016 1st Qtr 2016/2017
01/10/2016 to 31/12/2016 4th Qtr 2016 2nd Qtr 2016/2017
and so on
Period field at the moment ranges from December 2015 to March 2017
Period Cal_Yr_Qtr Fin_Yr_Qtr
MAR2017 1st Qtr 2017 3rd Qtr 2016/2017
FEB2017 1st Qtr 2017 3rd Qtr 2016/2017
JAN2017 1st Qtr 2017 3rd Qtr 2016/2017
DEC2016 4th Qtr 2016 2ndQtr 2016/2017
NOV2016 4th Qtr 2016 2ndQtr 2016/2017
DEC2015 4th Qtr 2015 2nd Qtr 2015/2016
@Reeza's method will definitely calculate quarter, but I think the following will come closer to what you said you wanted:
data have (drop=fy0); input Date ddmmyy10.; format date date9.; Cal_Yr_Qtr=put(date,yyqd.); FY0 = intnx('year.7',date,0,'B'); *start date of fiscal year; length Fin_Yr_Qtr $17; Fin_Yr_Qtr = catt('Qtr ',intck('QTR',fy0,date)+1,'-',year(fy0),'/',year(fy0)+1); cards; 01/01/2016 30/03/2016 01/05/2016 30/06/2016 01/07/2016 30/09/2016 01/10/2016 31/12/2016 ;
Art, CEO, AnalystFinder.com
Since the difference between calendar and fiscal is offset by 6 months, I would suggest using INTNX to advance/subtract date and then use the standard functions.
data test;
date='01Dec2016'd;
do i=0 to 11;
date=intnx('month', date, 1, 'b');
date_temp=intnx('month', date, -6, 'b');
calendar_quarter=qtr(date);
fiscal_quarter=qtr(date_temp);
format date date_temp date9.;
output;
end;
run;
proc print data=test;
;
run;
@Reeza's method will definitely calculate quarter, but I think the following will come closer to what you said you wanted:
data have (drop=fy0); input Date ddmmyy10.; format date date9.; Cal_Yr_Qtr=put(date,yyqd.); FY0 = intnx('year.7',date,0,'B'); *start date of fiscal year; length Fin_Yr_Qtr $17; Fin_Yr_Qtr = catt('Qtr ',intck('QTR',fy0,date)+1,'-',year(fy0),'/',year(fy0)+1); cards; 01/01/2016 30/03/2016 01/05/2016 30/06/2016 01/07/2016 30/09/2016 01/10/2016 31/12/2016 ;
Art, CEO, AnalystFinder.com
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.