topic Re: Coding with Fiscal Year in SAS Data Science

Coding with Fiscal Year

mlogan — Wed, 16 May 2018 13:27:44 GMT


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

Re: Coding with Fiscal Year

Ksharp — Wed, 16 May 2018 13:39:58 GMT

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;

Re: Coding with Fiscal Year

mlogan — Wed, 16 May 2018 14:20:59 GMT

Thanks Kharp. I wanted to query from the Year18_19 and Year17_18 dataset and get the following output:

From Year18_19:
----------------------

Date1 Course_ID age
09Apr2018 Che1 10
11MAY2018 Eng2 15
09Mar2018 Phy2 11

From Year17_18:
----------------------

Date1 Course_ID age
05Jan2018 ENG1 10
11MAY2017 Eng2 15
22Apr2017 Phy2 11
20Feb2018 Che1 15

Re: Coding with Fiscal Year

Ksharp — Thu, 17 May 2018 12:38:23 GMT

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;

Re: Coding with Fiscal Year

mlogan — Fri, 18 May 2018 19:09:38 GMT

I Ksharp, I actually wanted to make it dynamic so that at any given month when I will run my code, it will grab/filter the data until the last date of previous month of every year of the date field.

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 following code, but can really make it work at the end. Can you please help. Thanks.

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;

Re: Coding with Fiscal Year

Ksharp — Sat, 19 May 2018 12:41:48 GMT

"I actually wanted to make it dynamic so that at any given month "

according to what ? and How ?

Re: Coding with Fiscal Year

mlogan — Sat, 19 May 2018 19:05:11 GMT

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;

Re: Coding with Fiscal Year

art297 — Sat, 19 May 2018 17:35:43 GMT

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

Re: Coding with Fiscal Year

Ksharp — Sun, 20 May 2018 11:31:43 GMT

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;

Re: Coding with Fiscal Year

mlogan — Sun, 20 May 2018 19:31:23 GMT

Thanks Ksharp for your kind help.

Re: Coding with Fiscal Year

art297 — Mon, 21 May 2018 14:19:30 GMT

@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