05-15-2018 10:57 PM - edited 05-15-2018 10:58 PM
DATA Year18_19; INPUT Date1 Course_ID $4. age 3.; INFORMAT Date1 Date9.; FORMAT Date1 Date9.; DATALINES; 05Jan2018 ENG1 10 16Feb2018 Bio2 15 09Apr2018 Che1 10 15Jan2018 Bio1 11 11MAY2018 Eng2 15 09Mar2018 Phy2 11 20Feb2018 Che1 15 ; RUN; PROC PRINT DATA=Year18_19 NOOBS; RUN; DATA Year17_18; INPUT Date1 Course_ID $4. age 3.; INFORMAT Date1 Date9.; FORMAT Date1 Date9.; DATALINES; 05Jan2018 ENG1 10 16Jan2017 Bio2 15 09Feb2017 Che1 10 15Mar2018 Bio1 11 11MAY2017 Eng2 15 22Apr2017 Phy2 11 20Feb2018 Che1 15 ; RUN; PROC PRINT DATA=Year17_18 NOOBS; RUN;
My Fiscal Year goes from March 01 to February 30. When I am in the month of May now, I want to pull all the data for this fiscal year (from dataset Year18_19 above) UNTIL last month.
and I want the system to calculate that from sysdate.
So for this fiscal year code should return the following observations from dataset Year18_19:
Date1 Course_ID age
09Apr2018 Che1 10
11MAY2018 Eng2 15
09Mar2018 Phy2 11
Now I want to do the same for the last Fiscal year (Year17_18) which will return:
Date1 Course_ID age
05Jan2018 ENG1 10
11MAY2017 Eng2 15
22Apr2017 Phy2 11
20Feb2018 Che1 15
I tried to follow the following code to accomplish it, but I really can't get anything out of it. I will really appreciate any help on this.
%let previous_month = %sysfunc( intnx( MONTH , %sysfunc( today() ) , -1) , yymmN6.); %MACRO fy (date , start=7); year(&date) + (month(&date) ge &start and &start ne 1) %MEND; DATA try_FY; this_date = date(); FiscalYear = %FY( this_date , start = 3 ); PUTLOG FiscalYear = Txn_Date=date9.; RUN;
05-16-2018 03:13 PM
If your fiscal year began in January, you could just use the YEAR function to get fiscal year, as in
But since your fiscal year begins in March you could do the following: (1) get the closest march preceding DATE1, and (2) get the calendar year of that march, and call it fiscal year, as in:
data want; set year17_18 year18_19; fyear=year(intnx('year.3',date1,0,'B')); run;
The INTNX functions says to add a some units called "year.3" (years beginning in March) to DATE1. The 0 as third argument says to add zero such year, and the 'B' say to aligned the resulting date to the beginning of the year.3 time span. So Dec20, 2017 --> March 1, 2017 --> FYEAR=2017.
I've labeled fiscal years using the calendar year of their first month. So fiscal year March 2017-Feb 2018 is 2017.