BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Haydn
Quartz | Level 8

 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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@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

3 REPLIES 3
Reeza
Super User

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;
art297
Opal | Level 21

@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

Haydn
Quartz | Level 8
Thanks for all your help guys 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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