Calculating Average Number of Days

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Calculating Average Number of Days

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

 

 

 


Accepted Solutions
Solution
‎03-23-2018 11:22 PM
Trusted Advisor
Posts: 1,345

Re: Calculating Average Number of Days

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;

View solution in original post


All Replies
Super User
Posts: 23,774

Re: Calculating Average Number of Days

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

Trusted Advisor
Posts: 1,345

Re: Calculating Average Number of Days

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.

Contributor
Posts: 65

Re: Calculating Average Number of Days

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

Solution
‎03-23-2018 11:22 PM
Trusted Advisor
Posts: 1,345

Re: Calculating Average Number of Days

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;
Contributor
Posts: 65

Re: Calculating Average Number of Days

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

Jadallah

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 168 views
  • 1 like
  • 3 in conversation