topic Re: How to generate the fiscal year by 10K Filing date in SAS Data Management

How to generate the fiscal year by 10K Filing date

YuWeiLee — Mon, 17 Jun 2019 06:28:43 GMT

Hi everyone,

I am new to SAS, so there is a fundamental question that I want to ask for help.
I have the list of all company of SEC 10K Filing date as follows:

Filing Date

04/29/1993

11/12/1993

11/29/1993

My condition:

if the date is before 5/30 and it should be the fiscal year 1992, otherwise it is 1993

So the output will be

Filing Date Fiscal Year

04/29/1993 1992

11/12/1993 1993

11/29/1993 1993

Is there any way to get the right fiscal year?

Thanks

Re: How to generate the fiscal year by 10K Filing date

PeterClemmensen — Mon, 17 Jun 2019 06:38:47 GMT

Welcome to the SAS Communities 🙂

you can do like this

data have;
input FilingDate :mmddyy10.;
format FilingDate mmddyy10.;
datalines;
04/29/1993
11/12/1993
11/29/1993
;

data want;
    set have;
    if FilingDate ge mdy(5,30,year(FilingDate)) then FiscalYear=year(FilingDate);
    else FiscalYear=year(FilingDate)-1;
run;

Re: How to generate the fiscal year by 10K Filing date

Kurt_Bremser — Mon, 17 Jun 2019 06:41:38 GMT

Is it always May 30 or can that change in a leapyear?

Re: How to generate the fiscal year by 10K Filing date

YuWeiLee — Mon, 17 Jun 2019 06:47:30 GMT

It's always May 30.

Re: How to generate the fiscal year by 10K Filing date

Kurt_Bremser — Mon, 17 Jun 2019 06:54:39 GMT

Then you should take @PeterClemmensen 's solution.

A slight modification uses the ifn() function:

fiscalyear = ifn(filingdate ge mdy(5,30,year(filingdate)),year(filingdate),year(filingdate)-1));

Re: How to generate the fiscal year by 10K Filing date

FloydNevseta — Wed, 19 Jun 2019 12:31:19 GMT

If you are using the filing date from SEC's EDGAR database, then you can't determine with absolute certainty the fiscal year from the filing date of the 10-k. Those are always filed after the fiscal year ends to give companies time to compile the results and prepare the filing. For example, the company I work for filed their 10-k for FY ending 12/31 on 2/6/2019 a little over a month after.

Since the deadline for filing a 10-k with the SEC is generally 60-90 days after the fiscal year ends, a better approach would be...

year(FilingDate - 60)

... which is only an approximation but should work in most cases. For example, Google filed their latest 10-k on 2/6/19, so the FY calculation would correctly yield 2018. It also works for retailers like Target and Wal-Mart whose fiscal years end in January. Historically, their 10-k's are usually filed in March, so the calculation would correctly generate a fiscal year in the same year as the filing.

Re: How to generate the fiscal year by 10K Filing date

YuWeiLee — Wed, 19 Jun 2019 13:12:09 GMT

Hi,

I think you are right, and I forget that my date isn't the end of the fiscal date! Could you tell me how I can modify the code base on draycut's? I am not familiar with SAS and afraid that I enter the wrong code

Is it right?

if FDATE ge mdy(5,30,year(FDATE - 60)) then FiscalYear=year(FDATE - 60);
else FiscalYear=year(FDATE - 60);

Many thanks!!

Re: How to generate the fiscal year by 10K Filing date

FloydNevseta — Wed, 19 Jun 2019 14:28:10 GMT

Another complication: If you look in the section "Notes to Consolidated Financial Statements", there's a paragraph that describes how the financial data is consolidated including a definition of fiscal year. Target describes fiscal year 2018 as ending 2/2/2019 while Wal-mart defines fiscal 2019 as ending 1/31/2019. So while both companies use Feb-Jan fiscal year, each defines the year differently. Good luck.