Coding with fiscal year ends

jjadall1 — Tue, 17 Oct 2017 17:05:13 GMT

Hello SAS Support Communities,

Please see the code below. For your information, cik in the code is a unique identifier for each company (e.g., Lowe's, Home Depot, etc.) Let me give you an example of what I'm looking for. Let's say a company has a 12/31 fiscal year-end. There's a restatement that covers January 1, 2009 (rpb in the code below) through June 30, 2012 (rpe in the code below) that is disclosed on June 15, 2014.

I would like 3 versions of a categorical dependent variable in the same dataset as follows:

One with a categorical dummy variable = 1 if restatement_key (identifier of each restatement (January 1, 2009 through June 30, 2012 in my example)) is in year 1 of the restatement. That would be if first.restatement key then do, correct? In my example, the only categorical dependent variable with a value of 1 would be fyear = 2009.

One with a categorical dummy variable = 1 if fyear is between y1 and y2. In my example, the categorical dependent variable would have a value of 1 when fyear = 2009, 2010, 2011, and 2012.

One with a categorical dummy variable = 1 only in the year of disclosure. In my example, the categorical dependent variable would have a value of 1 when fyear = 2014.

data Etr.restatement1;
set Etr.restatement;
rpb = restated_period_begin;
rpe = restated_period_ended;
RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;
y1 = year(restated_period_begin);
y2 = year(restated_period_ended);
y3 = year(disclosure_date);
run;

data Etr.restatement1a;
set Etr.restatement1;
If RstateDays LT 350 then delete;
run;

data Etr.restatement2;
set Etr.restatement1a;
drop rpb;
drop rpe;
run;

proc sort data=Etr.restatement2 out=Etr.restatement2a;
by cik;
run;

/*create observation for each year restated [include begin year and end year] */
data Etr.restatement3;
set Etr.restatement2a;
by cik;
if cik then do;
do fyear=y1 to y3;
output;
end;
end;
run;

proc sort data= Etr.restatement3 out= Etr.restatement4 noduplicate;
by restatement_key cik fyear;
run;

data Etr.restatement5;
set Etr.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data Etr.restatement6;
set Etr.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data Etr.restatement7;
set Etr.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;

I was able to get the code to work IF a company has a 12/31 fiscal year-end. The restatement5 dataset generates dependent variable #1 (FO). The restatement6 dataset generates dependent variable #3 (AD). The restatement7 dataset generates dependent variable #2 (AO).

A problem sometimes occurs when a company does NOT have a 12/31 fiscal year end. Look at the attached restatement7 dataset. Observations 1 through 6 are good. However, look at observation 7 for example. For dependent variable #1 (FO), the company has a fiscal year-end of 5/31 (fiscal_month_end column). The restatement started on 7/1/2012. Therefore, the first occurrence (FO) should be for the 2013 fiscal year (since 7/1/2012 is between 6/1/2012 and 5/31/2013).

For dependent variable #2 (AO), look at observations 7 through 10. Observation 7 should NOT have a value of 1 because the restatement period began 7/1/2012 (which is after the 5/31/2012 cutoff). Observations 8 through 10 should have a value of 1 because of the following:

Restatement covers a time in between 6/1/2012 and 5/31/2013 (2013 fiscal year)

Restatement covers a time in between 6/1/2013 and 5/31/2014 (2014 fiscal year)

Restatement covers a time in between 6/1/2014 and 5/31/2015 (2015 fiscal year)

For dependent variable #3 (AD), 6/29/2015 falls in between 6/1/2015 and 5/31/2016 (2016 fiscal year). Therefore, observations 7 through 10 should all NOT have a value of 1. An additional observation should be created (2016 fiscal year) and that observation should have a value of 1.

How do I update my code to include these issues? The original dataset (restatement) is attached for you to work with.

God bless, best regards, and thank you so much for your help,
Jadallah

Re: Coding with fiscal year ends

jjadall1 — Wed, 18 Oct 2017 15:41:46 GMT

Hello,

It worked, thank God! Here's the code:

data Etr.restatement1;
set Etr.restatement;
rpb = restated_period_begin;
rpe = restated_period_ended;
RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;
if month(restated_period_begin) le fiscal_month_end then y1 = year(restated_period_begin);
else y1 = year(restated_period_begin) + 1;
if month(restated_period_ended) le fiscal_month_end then y2 = year(restated_period_ended);
else y2 = year(restated_period_ended) + 1;
if month(disclosure_date) le fiscal_month_end then y3 = year(disclosure_date);
else y3 = year(disclosure_date) + 1;
run;

data Etr.restatement1a;
set Etr.restatement1;
If RstateDays LT 350 then delete;
run;

data Etr.restatement2;
set Etr.restatement1a;
drop rpb;
drop rpe;
run;

proc sort data=Etr.restatement2 out=Etr.restatement2a;
by cik;
run;

/*create observation for each year restated [include begin year and end year] */
data Etr.restatement3;
set Etr.restatement2a;
by cik;
if cik then do;
do fyear=y1 to y3;
output;
end;
end;
run;

proc sort data= Etr.restatement3 out= Etr.restatement4 noduplicate;
by restatement_key cik fyear;
run;

data Etr.restatement5;
set Etr.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data Etr.restatement6;
set Etr.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data Etr.restatement7;
set Etr.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;

topic Re: Coding with fiscal year ends in SAS Programming

Coding with fiscal year ends

Re: Coding with fiscal year ends