05-19-2018 03:20 PM
Here is what my data situation and what I want.
My fiscal year goes from March 1 to end of February. At any given day (let's say today) I have my current fiscal year data until today (Mar 01 2018 - May 19 2018) and data from last 2 full fiscal year.
For example today I have:
FiscalYear 2018-2019: Mar 01 2018 - May 19 2019
Fiscal Year 2017-2018: Mar 01 2017 - Feb 28 2018
Fiscal Year 2016-2017: Mar 01 2016 - Feb 28 2017
WHAT I WANT:
Filter the data until the end of last month (in my case April of 2018-2019 Fiscal Year) and I want the code to determine that from system date.
Filter the data from each last 2 fiscal year until the end of April of each year. In my example it will be April 2017 & April 2016.
Every month when I will run my code, I don't want to change my Year/Fiscal year manually on my code.
My output table should look like this:
HERE IS MY Example DATA
INPUT Date Course_ID $4.;
INFORMAT Date Date9.;
FORMAT Date Date9.;
PROC PRINT NOOBS;
a month ago
Translate each filtering condition with SAS date interval functions:
data want; set have; if /* Before the end of last month */ date <= intnx("month", today(), -1, "end") and /* During last two fiscal years */ intck("year.3", date, today()) <= 2 and /* Within the first two months of the fiscal year */ intck("month", intnx("year.3", date, 0), date) < 2; run;
a month ago
It helps to show the desired result. Since your desired result is date of execution dependent it would be a very good idea to explicitly state the date of the program execution for future reference when a search leads someone to this thread.
I have to say I am not quite understanding what all your April references are getting to.
I suppose it has something to do with why 05Jan2018, 28Feb2018 and 09Feb2017 is excluded but the reasoning is not quite clear.