topic Coding with multiple Fiscal Year Data in SAS Programming

Coding with multiple Fiscal Year Data

mlogan — Wed, 16 May 2018 02:58:22 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

I tried to follow the following code to accomplish it, but I really can't get anything out of it. I will really appreciate any help on this.

Thanks,

%let previous_month = %sysfunc(
intnx( MONTH , %sysfunc( today() ) , -1)
, yymmN6.);



%MACRO fy (date , start=7);
year(&date) + (month(&date) ge &start and &start ne 1)
%MEND;



DATA try_FY;
this_date = date();
FiscalYear = %FY( this_date , start = 3 );
PUTLOG FiscalYear = Txn_Date=date9.;
RUN;

Re: Coding with multiple Fiscal Year Data

tomrvincent — Wed, 16 May 2018 17:01:46 GMT

This should help you: http://www2.sas.com/proceedings/sugi25/25/cc/25p084.pdf

Re: Coding with multiple Fiscal Year Data

mkeintz — Wed, 16 May 2018 19:13:52 GMT

If your fiscal year began in January, you could just use the YEAR function to get fiscal year, as in

fiscal_year=year(date1);

But since your fiscal year begins in March you could do the following: (1) get the closest march preceding DATE1, and (2) get the calendar year of that march, and call it fiscal year, as in:

data want;
  set year17_18 year18_19;
  fyear=year(intnx('year.3',date1,0,'B'));
run;

The INTNX functions says to add a some units called "year.3" (years beginning in March) to DATE1. The 0 as third argument says to add zero such year, and the 'B' say to aligned the resulting date to the beginning of the year.3 time span. So Dec20, 2017 --> March 1, 2017 --> FYEAR=2017.

I've labeled fiscal years using the calendar year of their first month. So fiscal year March 2017-Feb 2018 is 2017.