Dear Reeza, PGStats suggested the following solution. But I am not sure whether the code runs 1000 simulated distribution. Then I do not think the code has calculated 95th percentile from the distribution of 1000 peak 24-month concentrations. Also The code do not give me industry wise 24-month concentrations measure. In the paper you suggested last time (David Cassell's paper), I did not find any code to generate 1000 simulated distributions of the number of mergers in each month for each industry with the contraint- the simulated total number of mergers in each industry must equal to the original total number of mergers in that industry. If possible, could you please give me the code to solve the problem? data mergerCounts;
infile "&sasforum.\datasets\Calculating wave of mergers.txt" missover;
input Month Year Aindus48 COUNT;
month = mdy(month,1,year);
format month yymm7.;
drop year;
run;
proc sort data=mergerCounts; by Aindus48 month; run;
/* Add zero counts for missing months */
data fullMergerCounts;
do until(last.Aindus48);
set mergerCounts(rename=(month=xMonth count=xCount)); by Aindus48;
/* Assume records start in Jan of first mentioned year */
month = coalesce(month, mdy(12,1,year(xMonth)-1));
do while (month < intnx('month',xMonth,-1));
count = 0;
month = intnx("month", month,1);
output;
end;
month = xMonth;
count = coalesce(xCount, 0);
output;
end;
/* Complete last year until Dec */
do while (month < mdy(12, 1, year(xMonth)));
count = 0;
month = intnx("month", month,1);
output;
end;
format month yymm7.;
keep Aindus48 month count;
run;
/* Create random permutations of the merger series */
data simulMergerCounts;
call streamInit(2163453);
set fullMergerCounts;
output; /* Original counts will be identified with missing permNo */
do permNo = 1 to 99;
rnd = rand("uniform");
output;
end;
run;
proc sort data=simulMergerCounts; by Aindus48 permNo rnd month; run;
/* 24 months rolling sum of counts */
data mergerWaveCounts;
array a{24} _temporary_;
n = 0;
do until(last.permNo);
set simulMergerCounts; by Aindus48 permNo;
a{1+mod(n,24)} = count;
n = n + 1;
if n >= 24 then waveCount = sum(of a{*});
output;
end;
drop n;
run;
proc sql;
/* Find maximum number of mergers in 24 months in original data */
create table waveMax as
select
Aindus48,
max(waveCount) as observedMaxWave
from mergerWaveCounts
where permNo is missing
group by Aindus48;
/* Find maximum number of mergers in 24 months in randomly permuted series */
create table simulWaveMax as
select
Aindus48,
max(waveCount) as simulMaxWave
from mergerWaveCounts
where permNo is not missing
group by Aindus48, permNo;
/* Find position of observed maximums within maximums from permuted series */
create table waveStats as
select
a.Aindus48,
sum(a.observedMaxWave > b.simulMaxWave) as nSmallerWaveCounts,
count(b.simulMaxWave) as nSimulWaveCounts,
(1 + calculated nSmallerWaveCounts) / ( 1 + calculated nSimulWaveCounts) * 100 as wavePercentile
from
waveMax as a inner join
simulWaveMax as b on a.Aindus48=b.Aindus48
group by a.Aindus48;
quit;
... View more
