topic Return Australian Financial Year Quarter and Calendar Year Quarter in SAS Programming

Return Australian Financial Year Quarter and Calendar Year Quarter

Haydn — Tue, 09 May 2017 01:59:08 GMT

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

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

Reeza — Tue, 09 May 2017 02:35:41 GMT

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;

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

art297 — Tue, 09 May 2017 02:49:52 GMT

@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

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

Haydn — Tue, 09 May 2017 05:43:51 GMT

Thanks for all your help guys 🙂