topic Re: Dynamic filtering of data till last month of the fiscal year in SAS Programming

Dynamic filtering of data till last month of the fiscal year

mlogan — Sat, 19 May 2018 19:20:18 GMT

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:

16Mar2016 Bio2

01Mar2018 Eng1
15Mar2018 Bio1
27Apr2018 Bio1

HERE IS MY Example DATA

DATA have;
INPUT Date Course_ID $4.;
INFORMAT Date Date9.;
FORMAT Date Date9.;
DATALINES;
05Jan2018 ENG1
16Aug2017 Bio2
11MAY2015 Eng2
28Feb2018 Geo2

16Mar2016 Bio2

01Mar2018 Eng1
09Feb2017 Che1
19Jan2015 Phy2
15Mar2018 Bio1
12Mar2015 Phy2
19May2018 Che1
27Apr2018 Bio1
;
PROC PRINT NOOBS;
RUN;

Re: Dynamic filtering of data till last month of the fiscal year

PGStats — Sat, 19 May 2018 22:15:27 GMT

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;

Re: Dynamic filtering of data till last month of the fiscal year

ballardw — Mon, 21 May 2018 14:46:58 GMT

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.