topic Re: Dynamic filtering of data until last month of every year in SAS Programming

Dynamic filtering of data until last month of every year

mlogan — Sat, 19 May 2018 19:15:37 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 until last month of every year

SuryaKiran — Fri, 18 May 2018 19:31:35 GMT

What is the output your expecting? You don't need %SYSFUNC in your code.

Re: Dynamic filtering of data until last month of every year

Reeza — Fri, 18 May 2018 19:57:00 GMT

Shouldn’t your condition be the opposite?

It’s hard to follow your code an logic, for me at least, but I think you could just filter on the month? Then all the relevant months are included and you can ignore the concept of days or years.

Where month(date)  <= month(intnx(‘month’, today(), -1));

Re: Dynamic filtering of data until last month of every year

ballardw — Fri, 18 May 2018 22:42:38 GMT

@mlogan wrote:

I want to write code which will dynamically filter the data until the last date of previous month of every year in the database ('date' field in this case) at any given month

Here is my input data

DATA have;
INPUT Date Course_ID $4.;
INFORMAT Date Date9.;
FORMAT Date Date9.;
DATALINES;
05Jan2018 ENG1
16Jan2017 Bio2
09Dec2016 Che1
15Jan2018 Bio1
11MAY2015 Eng2
12Mar2016 Phy2
20May2018 Che1
;
PROC PRINT NOOBS;
RUN;

I have written the follow code, but I can't make it work. can someone please help.

DATA want;
SET have;
Y0_FYb=YEAR(intnx('YEAR.4',date,0));
Y0_FYe=YEAR(intnx('YEAR.4',date,0,'e'));

Y0_fromdate=intnx('year.4',date,0,'b');
Y0_todate=intnx('MONTH',date,-1,'e');

Y1P_FY=YEAR(intnx('YEAR.4',date,0));
Y1P_fromdate=intnx('year.4',date,0,'b');
Y1P_todate=intnx('MONTH',date,-1,'e');

Rep_ToMonth=intnx('MONTH', date , -1, 'e');
fy2 = COMPRESS(Y0_FYb||"-"||Y0_FYe);
FORMAT Y0_fromdate
Y0_todate Rep_ToMonth date date9.;
PROC PRINT NOOBS;
RUN;

DATA Final;
SET want;
WHERE Rep_ToMOnth >= intnx('MONTH', %SYSFUNC(today()), -1, 'e');
RUN;
PROC PRINT NOOBS;
RUN;

Can't make it work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

VERY seldom does %sysfunc belong in a data step and almost never in an "open code", not part of code inside a %macro / %mend; definition.

Re: Dynamic filtering of data until last month of every year

mlogan — Sat, 19 May 2018 19:07:04 GMT

Sorry my post did not make much sense I just realized. Here is what my data situation and what I want. Thanks for your help.

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;