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;
What is the output your expecting? You don't need %SYSFUNC in your code.
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));
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.