BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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;

2 REPLIES 2
PGStats
Opal | Level 21

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;
PG
ballardw
Super User

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 2 replies
  • 922 views
  • 0 likes
  • 3 in conversation