Search code examples
datesasproc-sqlyearmonth

Getting month and fiscal year from columns SAS

I have a table with the following type of data

FISCAL_YEAR MTH Month_Name
FY2018 6 JUN
FY2017 5 MAY
FY2022 10 OCT

I want to get current fiscal year (i.e. FY2023 for this instance but FY2024 next year) and 1 month previous (i.e. currently MTH = 7 and/or Month_Name = 'JUL'). How do I get that in SAS PROC SQL?

Solution

  • Still not clear on your question but...

    If you want to calculate the current fiscal year, you can use INTNX with an offset. The following will calculate current fiscal year, using Oct 1 as fiscal year cutpoint:

    %let FY= %sysfunc(year(%sysfunc(intnx(year.10, %sysfunc(today()), 0, e)))) ;
%let PriorMonth= %sysfunc(month(%sysfunc(intnx(month,%sysfunc(today()),-1,b)))) ;

%put &=FY &=PriorMonth ;

    Returns:

    FY=2023 PRIORMONTH=7

    So if you like that, then in your PROC SQL you can use a WHERE clause like:

    where Fiscal_Year="FY&FY" and MTH=&PriorMonth