DATA Year18_19;
INPUT Date1 Course_ID $4. age 3.;
INFORMAT Date1 Date9.;
FORMAT Date1 Date9.;
DATALINES;
05Jan2018 ENG1 10
16Feb2018 Bio2 15
09Apr2018 Che1 10
15Jan2018 Bio1 11
11MAY2018 Eng2 15
09Mar2018 Phy2 11
20Feb2018 Che1 15
;
RUN;
PROC PRINT DATA=Year18_19 NOOBS;
RUN;
DATA Year17_18;
INPUT Date1 Course_ID $4. age 3.;
INFORMAT Date1 Date9.;
FORMAT Date1 Date9.;
DATALINES;
05Jan2018 ENG1 10
16Jan2017 Bio2 15
09Feb2017 Che1 10
15Mar2018 Bio1 11
11MAY2017 Eng2 15
22Apr2017 Phy2 11
20Feb2018 Che1 15
;
RUN;
PROC PRINT DATA=Year17_18 NOOBS;
RUN;
Hi,
My Fiscal Year goes from March 01 to February 30. When I am in the month of May now, I want to pull all the data for this fiscal year (from dataset Year18_19 above) UNTIL last month.
and I want the system to calculate that from sysdate.
So for this fiscal year code should return the following observations from dataset Year18_19:
Date1 Course_ID age
09Apr2018 Che1 10
11MAY2018 Eng2 15
09Mar2018 Phy2 11
Now I want to do the same for the last Fiscal year (Year17_18) which will return:
Date1 Course_ID age
05Jan2018 ENG1 10
11MAY2017 Eng2 15
22Apr2017 Phy2 11
20Feb2018 Che1 15
Sorry. It is still confused to me .
data key;
start=intnx('month',today(),-1,'b');
end=intnx('month',today(),-1,'e');
output;
start=mdy(3,1,year(today()));
end=mdy(3,31,year(today()));
output;
start=mdy(3,1,year(today())-1);
end=mdy(3,31,year(today())-1);
output;
start=mdy(3,1,year(today())-2);
end=mdy(3,31,year(today())-2);
output;
format start end date9.;
run;
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 sql;
select a.*
from have as a,key as b
where date between start and end;
quit;
DATA Year18_19;
INPUT Date1 Course_ID $4. age 3.;
INFORMAT Date1 Date9.;
FORMAT Date1 Date9.;
fiscal_year=year(intnx('year.3',date1,0));
DATALINES;
05Jan2018 ENG1 10
16Feb2018 Bio2 15
09Apr2018 Che1 10
15Jan2018 Bio1 11
11MAY2018 Eng2 15
09Mar2018 Phy2 11
20Feb2018 Che1 15
;
RUN;
As long as you get YEAR variable .
DATA Year18_19;
INPUT Date1 Course_ID $4. age 3.;
INFORMAT Date1 Date9.;
FORMAT Date1 Date9.;
fiscal_year=year(intnx('year.3',date1,0));
DATALINES;
05Jan2018 ENG1 10
16Feb2018 Bio2 15
09Apr2018 Che1 10
15Jan2018 Bio1 11
11MAY2018 Eng2 15
09Mar2018 Phy2 11
20Feb2018 Che1 15
;
RUN;
data want;
set Year18_19;
if fiscal_year=2018;
run;
proc print noobs;run;
"I actually wanted to make it dynamic so that at any given month "
according to what ? and How ?
Hi Ksharp,
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;
Does the following do what you want?:
DATA have;
INPUT Date Course_ID $4.;
INFORMAT Date Date9.;
FORMAT Date Date9.;
DATALINES;
05Jan2018 ENG1
16Jan2017 Bio2
11MAY2015 Eng2
28Feb2018 Geo2
01Mar2018 Eng1
09Feb2017 Che1
19Jan2015 Phy2
15Mar2018 Bio1
12Mar2015 Phy2
19May2018 Che1
;
data want;
set have;
if date ge intnx('year',intnx('year.3',today(),0,'b'),-2,'s');
run;
Art, CEO, AnalystFinder.com
Sorry. It is still confused to me .
data key;
start=intnx('month',today(),-1,'b');
end=intnx('month',today(),-1,'e');
output;
start=mdy(3,1,year(today()));
end=mdy(3,31,year(today()));
output;
start=mdy(3,1,year(today())-1);
end=mdy(3,31,year(today())-1);
output;
start=mdy(3,1,year(today())-2);
end=mdy(3,31,year(today())-2);
output;
format start end date9.;
run;
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 sql;
select a.*
from have as a,key as b
where date between start and end;
quit;
@mlogan: You've marked this question as 'solved' but, as the posts have been edited and contain different have and want files than they originally contained, I'm not sure if you actually ever got a solution that accomplishes whatever it is that you're trying to do.
If your data are in the form I saw in your most recent edits, I think that the following accomplishes the task as stated:
data Year18_19;
input Date1 Course_ID $4. age 3.;
informat Date1 Date9.;
format Date1 Date9.;
datalines;
05Jan2018 ENG1 10
16Feb2018 Bio2 15
09Apr2018 Che1 10
15Jan2018 Bio1 11
11MAY2018 Eng2 15
09Mar2018 Phy2 11
20Feb2018 Che1 15
;
run;
data Year17_18;
input Date1 Course_ID $4. age 3.;
informat Date1 Date9.;
format Date1 Date9.;
datalines;
05Jan2018 ENG1 10
16Jan2017 Bio2 15
09Feb2017 Che1 10
15Mar2018 Bio1 11
11MAY2017 Eng2 15
22Apr2017 Phy2 11
20Feb2018 Che1 15
;
RUN;
%macro getrecs(offset);
%let start=%substr(%sysfunc(year(%sysfunc(intnx(year,%sysfunc(intnx(year.3,%sysfunc(today()),&offset.,b)),0,s)))),3);
%let end=%substr(%sysfunc(year(%sysfunc(intnx(year,%sysfunc(intnx(year.3,%sysfunc(today()),&offset.,e)),0,s)))),3);
data want_year&start._&end.;
set year&start._&end.;
if %sysfunc(intnx(year,%sysfunc(intnx(year.3,%sysfunc(today()),&offset.,b)),0,s)) le
date1 le %sysfunc(intnx(year,%sysfunc(intnx(year.3,%sysfunc(today()),&offset.,e)),-0,s));
run;
%mend getrecs;
%getrecs(0) /*to get current year*/
%getrecs(-1) /*to get previous year*/
Art, CEO, AnalystFinder.com
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.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.