DATA Step, Macro, Functions and more

Return Australian Financial Year Quarter and Calendar Year Quarter

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Return Australian Financial Year Quarter and Calendar Year Quarter

 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


Accepted Solutions
Solution
‎05-09-2017 01:24 AM
PROC Star
Posts: 7,487

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

@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

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

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;
Solution
‎05-09-2017 01:24 AM
PROC Star
Posts: 7,487

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

@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

Contributor
Posts: 47

Re: Return Australian Financial Year Quarter and Calendar Year Quarter

Thanks for all your help guys :-)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 261 views
  • 0 likes
  • 3 in conversation