Calculating Average Number of Days

[jjadall1]

Good morning SAS Technical Support,

I have two datasets, data1 (original example data) and data2 (what I want it to look like).  The variable I am looking to calculate is average delay.

data 1

CIK	fyr	rpb	rpe	disclosure_date	delay
12344	12	1/1/2009	12/31/2009	3/15/2011	439
12345	6	1/1/2010	6/30/2011	9/17/2011	79
21367	9	8/1/2011	9/30/2013	12/19/2013	80
31269	7	7/1/2013	8/31/2015	1/13/2016	135
43375	1	2/1/2014	7/31/2015	12/11/2015	133

data 2

CIK	fyr	rpb	rpe	disclosure_date	delay	average delay
12344	12	1/1/2009	12/31/2009	3/15/2011	439	439.00
12345	6	1/1/2010	6/30/2011	9/17/2011	79	261.50
21367	9	8/1/2011	9/30/2013	12/19/2013	80	445.33
31269	7	7/1/2013	8/31/2015	1/13/2016	135	432.00
43375	1	2/1/2014	7/31/2015	12/11/2015	133	223.50

I need to calculate the average number of delays a restatement is delayed from fiscal years.  The fiscal month-end of a company is the fyr variable.  For example, observation 1 has an fyr of 12.  That means 12/31 is the fiscal year-end.  Here's how the delay works that I'm looking for:

Observation 1

The restatement period is from 1/1/2009 (rpb) to 12/31/2009 (rpe).  This restatement is all only in one period since 12/31/2009 is the fiscal year-end.  Therefore, the average delay is the delay variable (number of days after 12/31/2009 to 3/15/2011 = 439).  12/31/2009 is NOT included in the calculation.  3/15/2011 is the disclosure date.

Observation 2

The restatement period is from 1/1/2010 (rpb) to 6/30/2011 (rpe).  This restatement is in two periods (1/1/2010 through 6/30/2010 and 7/1/2010 through 6/30/2011).  Therefore, the average delay is the average of the number of days after 6/30/2010 through 9/17/2011 (444 days) and the number of days after 6/30/2011 through 9/17/2011 (79), which is 261.50.

The other observations work the same way. 

Delay is calculated as follows:

Delay = (Datdif (rpe, disclosure_date, 'act/act'));

That delay calculation is only from the end of the restatement periods to the disclosure date.  I need the average of delay for all periods.

Counting number of periods based on fyr is done with the following code:

Periods=(intck(catt('qtr.',mod(fyr,3)+1),rpb,rpe)+1)/4;

That may help you with the fyr idea.

Please let me know if you have any questions.  Thank you so much for your help!

God bless, best regards, and have a fabulous weekend,

Jadallah

[Reeza]

Just a friendly note that this is not 'SAS Technical Support', this is a user community forum where almost everyone is a volunteer. There are several SAS employee's and you can tell who they are by their avatar. 

---

@jjadall1 wrote:

Good morning SAS Technical Support,

---

SAS Technical Support can be contacted by opening a ticket through their online system or emailing support@sas.com

[mkeintz]

This loops over a series of consecutive fiscal year end dates, starting with the RPB fiscal year end date, and ending with the RPE fiscal year end date.

data have;
  input CIK fyr (rpb rpe disclosure_date) (:mmddyy10.) delay ;
  format rpb rpe disclosure_date date9.;
datalines;
12344 12 1/1/2009 12/31/2009 3/15/2011 439 
12345 6 1/1/2010 6/30/2011 9/17/2011 79 
21367 9 8/1/2011 9/30/2013 12/19/2013 80 
31269 7 7/1/2013 8/31/2015 1/13/2016 135 
43375 1 2/1/2014 7/31/2015 12/11/2015 133 
run;

data want (drop=fyr_:);
  set have;
  fyr_enddate_final=intnx(cats('year.',mod(fyr,12)+1),rpe,0,'END');
  fyr_enddate=intnx(cats('year.',mod(fyr,12)+1),rpb,0,'END');

  do N_periods_disclosed=1 by 1 until (fyr_enddate > fyr_enddate_final);
    average_days=sum(average_days,disclosure_date-fyr_enddate);
    fyr_enddate=intnx('month',fyr_enddate,12,'END');
  end;
  average_delay=average_days/N_periods_disclosed;
run;

The program matches your posted average delays for the first 3 records, but not the last 2.   Are you sure you posted the correct results for #4 and #5? 

Also DATDIF with 'ACT/ACT' is just a count of days.  And since sas date values are numbers representing number of days after or before 01jan1960, a simple subtraction works as well.  But you could replace

    average_days=sum(average_days,disclosure_date-fyr_enddate);

with

    average_days=sum(average_days,DATDIF(fyr_enddate,disclosure_date,"ACT/ACT"));

if you prefer.

[jjadall1]

Hello,

Thank you for your help!  You are right - the first 3 match.  The issue (if I'm not mistaken) with the last two is that the end of the restatement period (rpe) does NOT match to the fyr.  If that's the case, the delay for that last period is the delay variable that I already have calculated.  How would I incorporate that?

God bless, best regards, and thanks again,

Jadallah

[mkeintz · Accepted solution]

So when end-date of the last fiscal year would be AFTER disclosure date, you want to use the DELAY value rather than the calculated difference (which would be negative) between fiscal year end and disclosure.  In that case you can add maximum of delay vs calculated difference. The calculated difference will be the maximum for all non-final years, and also for final years in which the period end aligns with a fiscal year end date.  For other final years, delay is the maximum.

data have;
  input CIK fyr (rpb rpe disclosure_date) (:mmddyy10.) delay ;
  format rpb rpe disclosure_date date9.;
datalines;
12344 12 1/1/2009 12/31/2009  3/15/2011 439
12345  6 1/1/2010  6/30/2011  9/17/2011  79
21367  9 8/1/2011  9/30/2013 12/19/2013  80
31269  7 7/1/2013  8/31/2015  1/13/2016 135
43375  1 2/1/2014  7/31/2015 12/11/2015 133
run;

data want (drop=fyr_:);
  set have;
  fyr_enddate_final=intnx(cats('year.',mod(fyr,12)+1),rpe,0,'END');
  fyr_enddate=intnx(cats('year.',mod(fyr,12)+1),rpb,0,'END');

  do N_periods_disclosed=1 by 1 until (fyr_enddate > fyr_enddate_final);
    fyr_delay=max(delay,disclosure_date-fyr_enddate);
    fyr_total_days=sum(fyr_total_days,fyr_delay);
    fyr_enddate=intnx('month',fyr_enddate,12,'END');
  end;
  average_delay=fyr_total_days/N_periods_disclosed;
run;

[jjadall1]

Thank you so very much!!!  God bless you and your family!!!!!

Jadallah