DATA Step, Macro, Functions and more

How to set fiscal year2019 [Apr2018 to Mar2019]

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to set fiscal year2019 [Apr2018 to Mar2019]

I have to set start_date and end_date from the cycle date.

If the Cycle Date: 28th April 2018 then the:

 

  • Start Month: Apr 2016 – Exactly 2 years backdated. the below code I have fix for that.

SAS CODE: 

%let Curr_Date= '28APR2018'd;

start_date=intnx('year', &Curr_Date, -2,"sameday");

  

Similarly the end_date should always be Jan with respective year from cycle date and fiscal year

For Example:

 

  • End_date: Jan 2016 –>   should always be Jan 2016 for the fiscal year2017 [Apr2016 to Mar2017]
  • End_date: Jan 2018 –>   should always be Jan 2018 for the fiscal year2019 [Apr2018 to Mar2019]

Can you please provide me some sample code how to fix it?


Accepted Solutions
Solution
2 weeks ago
Trusted Advisor
Posts: 1,312

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

Is this a correct understanding of your requirements?

 

  1. Every sample date belongs to a fiscal year (01aprxxxx-31marxxxy) where xxxy=xxxx+1.
  2. start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).
  3. enddate is 01janxxxx,

If these are your rules then you could construct the first day of the fiscal year containing sample_date as

fy_start_date=intnx('year.4',sample_date,0,'begin');

 

The YEAR.4 interval is a year beginning on April 01.  So the above adds 0 fiscal years to sample_date, and aligns the result to the beginning of the fiscal year - i.e. 01APR.  Then it's easy to get 01JAN of the same year.  For instance:

enddate=mdy(1,1,year(fy_start_date);

 

Or in a single assignment

enddate=mdy(1,1,year(intnx('year.4',sample_date,0,'begin')));

View solution in original post


All Replies
Respected Advisor
Posts: 4,687

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

@Purna

Not sure that I've understood the logic you're after. Does below return your desired result?

data test;
  format 
    have_dt date9.
    start_fisc_dt date9.
    want_dt date9.
    ;
  input have_dt :date9.;

  /* start of fiscal year for have_dt */
  start_fisc_dt=intnx('year.4',have_dt,0,'b');

  /* 01 january date in calendar date of start date of fiscal year */
  want_dt=intnx('year.4',start_fisc_dt,0,'b');

  datalines;
01JAN2016
31MAR2016
01APR2016
31DEC2016
01JAN2017
;
run;

Capture.JPG

Occasional Contributor
Posts: 15

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

[ Edited ]

Hi

Actually my concern is  I have a  Cycle Date: 28th April 2018.

 

Start_date should be trigger from  : Apr 2016 – Exactly 2 years backdated

 

end_date  should be dynamically change based on fiscal year.

Example:

 

End_date: Jan 2017 – should always be Jan 2017 for the fiscal year2018 [Apr2016 to Mar2017]

Again the end_date will change from [Apr 2017 to Mar2018] the end_date it should be : Jan2018

 

Does that make sense for your understanding.

I wrote the below SAS code , however I m not sure end_date output it has be resolve two times in dynamically

 

 

SAS CODE:

 

%let Curr_Date= '28APR2018'd;
%let sysyear= %sysfunc(year("&Curr_Date"d));
%put  &sysyear;

 

data test;
file print;
format date end_date start_date date9.;

do month=1 to 12;
   start_date=intnx('year', &Curr_Date, -2,"sameday");

   date=mdy(month,1,&sysyear);

   fedfiscalyear = year(start_date) + (month(date) le 3);
   end_date="01JAN&sysyear"d;

   put date= mmddyy10. fedfiscalyear=   ;

end;
    call symputx("startdate",start_date);
 call symputx("end_date",end_date); 
run;
%put &startdate. &end_date.;

Respected Advisor
Posts: 4,687

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

@Purna

I'm still not sure that I understand. Using the data I've posted why don't you show us what the desired want_dt should be when using your logic.

 

From what you describe you should be able to use the INTNX() function also for your end_date calculation.

 

 

 

Solution
2 weeks ago
Trusted Advisor
Posts: 1,312

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

Is this a correct understanding of your requirements?

 

  1. Every sample date belongs to a fiscal year (01aprxxxx-31marxxxy) where xxxy=xxxx+1.
  2. start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).
  3. enddate is 01janxxxx,

If these are your rules then you could construct the first day of the fiscal year containing sample_date as

fy_start_date=intnx('year.4',sample_date,0,'begin');

 

The YEAR.4 interval is a year beginning on April 01.  So the above adds 0 fiscal years to sample_date, and aligns the result to the beginning of the fiscal year - i.e. 01APR.  Then it's easy to get 01JAN of the same year.  For instance:

enddate=mdy(1,1,year(fy_start_date);

 

Or in a single assignment

enddate=mdy(1,1,year(intnx('year.4',sample_date,0,'begin')));
Occasional Contributor
Posts: 15

Re: How to set fiscal year2019 [Apr2018 to Mar2019]

start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).

That's correct it is start day. Now I am able to calculate end date based on fiscal year. It is working fine for me.

thank you very much.  Sorry for delayed your response due to family matter.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 123 views
  • 0 likes
  • 3 in conversation