DATA Step, Macro, Functions and more

Coding with multiple Fiscal Year Data

Reply
Regular Contributor
Posts: 243

Coding with multiple Fiscal Year Data

[ Edited ]

 


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;

 

Regular Contributor
Posts: 181

Re: Coding with multiple Fiscal Year Data

Trusted Advisor
Posts: 1,312

Re: Coding with multiple Fiscal Year Data

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.

Ask a Question
Discussion stats
  • 2 replies
  • 44 views
  • 0 likes
  • 3 in conversation