Good morning SAS Technical Support,
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 |
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;
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
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.
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
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;
Thank you so very much!!! God bless you and your family!!!!!
Jadallah
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.