topic Re: fiscal year manipulation in SAS Programming

fiscal year manipulation

sharonlee — Tue, 13 Oct 2020 17:27:05 GMT

Hi,
I've looked extensively at this forum for how to convert (calendar) years into fiscal year format, but I'm having problems with the formatting and data manipulation of the fiscal years.

In my code I can use the year function to get years, but this is calendar years:

format calendar_year Best12.;
calendar_year=compress(year(date));

I then create a new variable called "combine" that just has the calendar_year with a "check" text:

format combine $20.
combine = compress(calendar_year||"check");

The result is what I want: "2019check" Note that I see the year displayed correctly.

However, when I try to convert my code into fiscal year, using what I learned from this forum, I don't get what I want.

I do the following:

format fiscal_year year. ;
fiscal_year = intnx('YEAR.4',date,0);

If I proc print fiscal_year, it displays correct e.g., 2019.

Then I try to create the combine2 variable as follows:

format combine2 $20.;
combine2 = compress(fiscal_year||"wrong");

My result is the SAS date format as follows: 1524wrong. I would like it to display as 2019wrong.

What am I doing wrong?

Thanks in advance.

Re: fiscal year manipulation

ballardw — Tue, 13 Oct 2020 17:39:06 GMT

Compress knows nothing of the formats involved

so when you use:

fiscal_year = intnx('YEAR.4',date,0);
format combine2 $20.;
combine2 = compress(fiscal_year||"wrong");

Then compress is using the numeric value of the date fiscal_year. If you want a character function, such as Compress, to use the "proper" value then you need to explicitly convert the number to character.

combine2 = compress(put(fiscal_year, year4.)||"wrong");

Now, for your

I've looked extensively at this forum for how to convert (calendar) years into fiscal year format, but I'm having problems with the formatting and data manipulation of the fiscal years.

You do not provide any examples of the fiscal year rules or what you expect to get with "formatting".

For many purposes it may be sufficient to have a Fiscal Year variable. Which can usually be achieved by incrementing year of a date after a given date. For example the US Federal Fiscal year that changes in October:

data example;
   date = '15OCT2020'd;
   calyear= year(date);
   FedFiscalYear = year(date)+(month(date) ge 10);
run;

But details on what you need will help.

Re: fiscal year manipulation

SASJedi — Tue, 13 Oct 2020 17:54:11 GMT

fiscal_year (the result of your INTNX function call) is a SAS date value, not just a year value. You want fiscal_year as a character value representing the year, just like the character value you built for calendar_year. Try this instead:

data test;
   format date mmddyy10. calendar_year fiscal_year best12. combine combine2 $20.;
   date='01jan2019'd;
   calendar_year= put(date,year4.);
   fiscal_year = put(intnx('YEAR.4',date,0),year4.);
   combine = cats(calendar_year,"check");
   combine2 = cats(fiscal_year,"check");
   output;
   date='01jun2019'd;
   calendar_year= put(date,year4.);
   fiscal_year = put(intnx('YEAR.4',date,0),year4.);
   combine = cats(calendar_year,"check");
   combine2 = cats(fiscal_year,"check");
   output;
run;
proc print;
run;

This is the result:

Obs	date	calendar_year	fiscal_year	combine	combine2
1	1/1/2019	2019	2018	2019check	2018check
2	6/1/2019	2019	2019	2019check	2019check

Re: fiscal year manipulation

sharonlee — Tue, 13 Oct 2020 19:51:46 GMT

Thank you for reminding me the importance of using the "put" statement!