BookmarkSubscribeRSS Feed
nazmul
Quartz | Level 8

Hello Everyone,

 

I have panel data for 48 industry wide monthly number of mergers data from 1978 to 2016. An industry may find 0 to any number of mergers in each month. I want to generate 1000 simulated distributions of the number of mergers in each month for each industry with the simulated total number of mergers in each industry equal to the original total number of mergers in that industry. Say, Airlines industry originally had total 200 mergers from 1978 to 2016. The each 1000 simulated distribution of the number of merges in airlines industry should have in total 200 mergers. I do not know what kind of distribution it would be (uniform, binomial etc). Most probably, it should uniform distribution. But all the simulated data should be integers.

 

2) Then I want to calculate the highest 24-month concentration of activity from each of the 1000 draws. For this I have to calculate the rolling 24 months total number of mergers in each industry in all the 1000 simulated distribution. Then I have to pick the month on which the total number of mergers is highest in each industry. So, I will get 1000 peak 24-month concentrations from 1000 simulated distributions for each industry.

 

3) Then I have to calculate 95th percentile from the distribution of 1000 peak 24-month concentrations

 

I put a sample data in here

 

The sample data has 3 industries. Industry 1 had 49 mergers over the time period. So all the simulated distribution must have in total 49 mergers for industry 1. The same rule applies for other industries also.

 

I am struggling with this problem for the last 3 days and I tried my best to solve it. I could not find a way to do it. Please kindly give me the code which can solve the problem. Also, if possible please explain the code to me so that I can make the necessary change if I have to make. For example, I may reduce the current rime period (1980 to 2016) to 1980-1990 and so. So, if you can kindly explain the code it will be easy for me to make the necessary changes.

 

Aindus48datecount
1Jan-800
1Feb-800
1Mar-800
1Apr-800
1May-800
1Jun-800
1Jul-800
1Aug-800
1Sep-800
1Oct-800
1Nov-800
1Dec-800
1Jan-810
1Feb-810
1Mar-810
1Apr-810
1May-811
1Jun-810
1Jul-810
1Aug-810
1Sep-810
1Oct-810
1Nov-810
1Dec-810
1Jan-820
1Feb-820
1Mar-820
1Apr-821
1May-820
1Jun-820
1Jul-820
1Aug-820
1Sep-820
1Oct-820
1Nov-820
1Dec-820
1Jan-830
1Feb-830
1Mar-830
1Apr-830
1May-830
1Jun-830
1Jul-830
1Aug-830
1Sep-831
1Oct-830
1Nov-830
1Dec-830
1Jan-840
1Feb-840
1Mar-840
1Apr-840
1May-841
1Jun-840
1Jul-840
1Aug-840
1Sep-840
1Oct-840
1Nov-840
1Dec-841
1Jan-850
1Feb-851
1Mar-850
1Apr-850
1May-850
1Jun-850
1Jul-850
1Aug-850
1Sep-850
1Oct-850
1Nov-850
1Dec-850
1Jan-860
1Feb-860
1Mar-860
1Apr-860
1May-860
1Jun-860
1Jul-860
1Aug-860
1Sep-860
1Oct-860
1Nov-860
1Dec-860
1Jan-870
1Feb-870
1Mar-870
1Apr-870
1May-870
1Jun-870
1Jul-870
1Aug-870
1Sep-870
1Oct-870
1Nov-870
1Dec-870
1Jan-880
1Feb-880
1Mar-880
1Apr-880
1May-880
1Jun-880
1Jul-880
1Aug-880
1Sep-880
1Oct-880
1Nov-880
1Dec-880
1Jan-890
1Feb-891
1Mar-891
1Apr-890
1May-890
1Jun-890
1Jul-890
1Aug-890
1Sep-890
1Oct-890
1Nov-890
1Dec-890
1Jan-901
1Feb-900
1Mar-900
1Apr-900
1May-900
1Jun-900
1Jul-900
1Aug-900
1Sep-900
1Oct-900
1Nov-900
1Dec-900
1Jan-910
1Feb-910
1Mar-910
1Apr-910
1May-910
1Jun-910
1Jul-911
1Aug-911
1Sep-910
1Oct-911
1Nov-910
1Dec-910
1Jan-920
1Feb-921
1Mar-920
1Apr-920
1May-920
1Jun-920
1Jul-920
1Aug-920
1Sep-920
1Oct-921
1Nov-920
1Dec-920
1Jan-930
1Feb-930
1Mar-930
1Apr-930
1May-930
1Jun-930
1Jul-930
1Aug-930
1Sep-930
1Oct-930
1Nov-930
1Dec-930
1Jan-940
1Feb-940
1Mar-940
1Apr-940
1May-940
1Jun-940
1Jul-940
1Aug-940
1Sep-940
1Oct-940
1Nov-940
1Dec-940
1Jan-950
1Feb-951
1Mar-950
1Apr-950
1May-950
1Jun-950
1Jul-950
1Aug-950
1Sep-950
1Oct-950
1Nov-950
1Dec-950
1Jan-960
1Feb-962
1Mar-962
1Apr-960
1May-962
1Jun-960
1Jul-960
1Aug-960
1Sep-961
1Oct-960
1Nov-960
1Dec-960
1Jan-970
1Feb-970
1Mar-970
1Apr-970
1May-970
1Jun-970
1Jul-970
1Aug-970
1Sep-970
1Oct-971
1Nov-970
1Dec-970
1Jan-980
1Feb-980
1Mar-980
1Apr-980
1May-981
1Jun-980
1Jul-981
1Aug-980
1Sep-980
1Oct-980
1Nov-981
1Dec-980
1Jan-990
1Feb-990
1Mar-992
1Apr-991
1May-990
1Jun-990
1Jul-990
1Aug-990
1Sep-990
1Oct-990
1Nov-990
1Dec-990
1Jan-000
1Feb-000
1Mar-000
1Apr-000
1May-000
1Jun-000
1Jul-000
1Aug-000
1Sep-000
1Oct-000
1Nov-000
1Dec-000
1Jan-010
1Feb-010
1Mar-010
1Apr-010
1May-010
1Jun-010
1Jul-010
1Aug-010
1Sep-010
1Oct-010
1Nov-010
1Dec-010
1Jan-020
1Feb-020
1Mar-020
1Apr-020
1May-020
1Jun-020
1Jul-020
1Aug-020
1Sep-020
1Oct-020
1Nov-020
1Dec-020
1Jan-030
1Feb-030
1Mar-030
1Apr-030
1May-030
1Jun-030
1Jul-030
1Aug-030
1Sep-030
1Oct-030
1Nov-030
1Dec-030
1Jan-040
1Feb-040
1Mar-041
1Apr-040
1May-041
1Jun-040
1Jul-041
1Aug-041
1Sep-040
1Oct-041
1Nov-040
1Dec-040
1Jan-050
1Feb-050
1Mar-050
1Apr-050
1May-051
1Jun-050
1Jul-051
1Aug-050
1Sep-050
1Oct-050
1Nov-050
1Dec-050
1Jan-060
1Feb-060
1Mar-060
1Apr-060
1May-060
1Jun-060
1Jul-060
1Aug-060
1Sep-060
1Oct-060
1Nov-060
1Dec-061
1Jan-070
1Feb-070
1Mar-070
1Apr-070
1May-071
1Jun-070
1Jul-070
1Aug-070
1Sep-070
1Oct-071
1Nov-070
1Dec-070
1Jan-080
1Feb-080
1Mar-080
1Apr-080
1May-080
1Jun-080
1Jul-080
1Aug-080
1Sep-080
1Oct-080
1Nov-080
1Dec-080
1Jan-090
1Feb-091
1Mar-090
1Apr-091
1May-090
1Jun-090
1Jul-092
1Aug-090
1Sep-090
1Oct-090
1Nov-090
1Dec-090
1Jan-100
1Feb-100
1Mar-100
1Apr-100
1May-100
1Jun-101
1Jul-100
1Aug-100
1Sep-100
1Oct-100
1Nov-100
1Dec-100
1Jan-110
1Feb-110
1Mar-110
1Apr-110
1May-110
1Jun-111
1Jul-110
1Aug-111
1Sep-110
1Oct-111
1Nov-111
1Dec-110
1Jan-120
1Feb-120
1Mar-120
1Apr-120
1May-120
1Jun-120
1Jul-120
1Aug-120
1Sep-120
1Oct-120
1Nov-120
1Dec-120
1Jan-130
1Feb-130
1Mar-130
1Apr-130
1May-130
1Jun-130
1Jul-130
1Aug-130
1Sep-131
1Oct-130
1Nov-130
1Dec-130
1Jan-140
1Feb-140
1Mar-140
1Apr-140
1May-140
1Jun-140
1Jul-140
1Aug-140
1Sep-140
1Oct-140
1Nov-140
1Dec-140
1Jan-150
1Feb-150
1Mar-150
1Apr-150
1May-150
1Jun-150
1Jul-150
1Aug-150
1Sep-150
1Oct-150
1Nov-150
1Dec-150
1Jan-160
1Feb-160
1Mar-160
1Apr-160
1May-160
1Jun-160
1Jul-160
1Aug-160
1Sep-160
1Oct-160
1Nov-160
1Dec-160
2Jan-800
2Feb-800
2Mar-800
2Apr-800
2May-800
2Jun-800
2Jul-800
2Aug-800
2Sep-800
2Oct-801
2Nov-800
2Dec-800
2Jan-810
2Feb-814
2Mar-810
2Apr-810
2May-810
2Jun-810
2Jul-811
2Aug-810
2Sep-810
2Oct-811
2Nov-810
2Dec-810
2Jan-821
2Feb-820
2Mar-820
2Apr-821
2May-820
2Jun-820
2Jul-821
2Aug-820
2Sep-820
2Oct-820
2Nov-820
2Dec-821
2Jan-830
2Feb-830
2Mar-830
2Apr-830
2May-830
2Jun-831
2Jul-831
2Aug-831
2Sep-831
2Oct-831
2Nov-830
2Dec-832
2Jan-841
2Feb-840
2Mar-841
2Apr-843
2May-841
2Jun-841
2Jul-843
2Aug-840
2Sep-841
2Oct-842
2Nov-840
2Dec-841
2Jan-850
2Feb-850
2Mar-850
2Apr-850
2May-850
2Jun-850
2Jul-850
2Aug-850
2Sep-850
2Oct-851
2Nov-850
2Dec-850
2Jan-861
2Feb-861
2Mar-861
2Apr-860
2May-860
2Jun-861
2Jul-860
2Aug-860
2Sep-862
2Oct-860
2Nov-860
2Dec-860
2Jan-870
2Feb-870
2Mar-872
2Apr-871
2May-870
2Jun-872
2Jul-870
2Aug-870
2Sep-871
2Oct-870
2Nov-870
2Dec-870
2Jan-880
2Feb-882
2Mar-881
2Apr-880
2May-880
2Jun-880
2Jul-880
2Aug-880
2Sep-880
2Oct-881
2Nov-880
2Dec-881
2Jan-891
2Feb-892
2Mar-890
2Apr-890
2May-890
2Jun-891
2Jul-890
2Aug-890
2Sep-890
2Oct-890
2Nov-891
2Dec-891
2Jan-901
2Feb-902
2Mar-900
2Apr-900
2May-900
2Jun-900
2Jul-900
2Aug-901
2Sep-900
2Oct-900
2Nov-900
2Dec-900
2Jan-910
2Feb-910
2Mar-910
2Apr-912
2May-912
2Jun-910
2Jul-910
2Aug-910
2Sep-911
2Oct-913
2Nov-910
2Dec-911
2Jan-921
2Feb-920
2Mar-920
2Apr-920
2May-920
2Jun-922
2Jul-920
2Aug-920
2Sep-922
2Oct-922
2Nov-920
2Dec-920
2Jan-930
2Feb-933
2Mar-930
2Apr-930
2May-930
2Jun-930
2Jul-931
2Aug-931
2Sep-930
2Oct-930
2Nov-930
2Dec-931
2Jan-940
2Feb-941
2Mar-940
2Apr-940
2May-940
2Jun-941
2Jul-940
2Aug-941
2Sep-940
2Oct-941
2Nov-942
2Dec-941
2Jan-950
2Feb-950
2Mar-951
2Apr-951
2May-950
2Jun-950
2Jul-950
2Aug-950
2Sep-950
2Oct-950
2Nov-952
2Dec-952
2Jan-960
2Feb-960
2Mar-960
2Apr-960
2May-964
2Jun-960
2Jul-960
2Aug-960
2Sep-961
2Oct-961
2Nov-960
2Dec-960
2Jan-970
2Feb-971
2Mar-971
2Apr-970
2May-970
2Jun-970
2Jul-971
2Aug-971
2Sep-973
2Oct-970
2Nov-970
2Dec-971
2Jan-984
2Feb-981
2Mar-981
2Apr-980
2May-980
2Jun-980
2Jul-980
2Aug-981
2Sep-980
2Oct-980
2Nov-980
2Dec-980
2Jan-990
2Feb-994
2Mar-990
2Apr-991
2May-990
2Jun-990
2Jul-990
2Aug-990
2Sep-990
2Oct-991
2Nov-990
2Dec-991
2Jan-001
2Feb-001
2Mar-000
2Apr-000
2May-000
2Jun-001
2Jul-001
2Aug-001
2Sep-001
2Oct-001
2Nov-000
2Dec-001
2Jan-010
2Feb-010
2Mar-010
2Apr-010
2May-011
2Jun-010
2Jul-011
2Aug-010
2Sep-012
2Oct-011
2Nov-010
2Dec-010
2Jan-020
2Feb-021
2Mar-020
2Apr-020
2May-022
2Jun-021
2Jul-021
2Aug-020
2Sep-020
2Oct-020
2Nov-020
2Dec-021
2Jan-030
2Feb-030
2Mar-031
2Apr-030
2May-030
2Jun-033
2Jul-030
2Aug-030
2Sep-030
2Oct-030
2Nov-031
2Dec-030
2Jan-040
2Feb-040
2Mar-041
2Apr-041
2May-042
2Jun-041
2Jul-041
2Aug-041
2Sep-040
2Oct-040
2Nov-040
2Dec-041
2Jan-050
2Feb-050
2Mar-051
2Apr-051
2May-050
2Jun-050
2Jul-050
2Aug-051
2Sep-050
2Oct-050
2Nov-050
2Dec-050
2Jan-060
2Feb-061
2Mar-061
2Apr-060
2May-062
2Jun-060
2Jul-061
2Aug-061
2Sep-063
2Oct-062
2Nov-060
2Dec-060
2Jan-070
2Feb-070
2Mar-070
2Apr-072
2May-070
2Jun-071
2Jul-070
2Aug-071
2Sep-071
2Oct-071
2Nov-072
2Dec-071
2Jan-080
2Feb-081
2Mar-080
2Apr-080
2May-080
2Jun-084
2Jul-080
2Aug-080
2Sep-080
2Oct-080
2Nov-080
2Dec-080
2Jan-090
2Feb-090
2Mar-090
2Apr-091
2May-090
2Jun-090
2Jul-091
2Aug-090
2Sep-090
2Oct-090
2Nov-091
2Dec-090
2Jan-100
2Feb-100
2Mar-100
2Apr-100
2May-100
2Jun-102
2Jul-101
2Aug-100
2Sep-101
2Oct-100
2Nov-101
2Dec-100
2Jan-111
2Feb-110
2Mar-110
2Apr-110
2May-110
2Jun-111
2Jul-110
2Aug-111
2Sep-111
2Oct-110
2Nov-110
2Dec-110
2Jan-120
2Feb-120
2Mar-120
2Apr-120
2May-121
2Jun-120
2Jul-120
2Aug-120
2Sep-120
2Oct-120
2Nov-121
2Dec-120
2Jan-130
2Feb-131
2Mar-130
2Apr-130
2May-130
2Jun-130
2Jul-131
2Aug-130
2Sep-132
2Oct-130
2Nov-130
2Dec-130
2Jan-140
2Feb-140
2Mar-140
2Apr-141
2May-142
2Jun-141
2Jul-141
2Aug-141
2Sep-141
2Oct-141
2Nov-140
2Dec-140
2Jan-150
2Feb-150
2Mar-152
2Apr-150
2May-150
2Jun-151
2Jul-150
2Aug-150
2Sep-150
2Oct-151
2Nov-152
2Dec-150
2Jan-160
2Feb-160
2Mar-160
2Apr-160
2May-160
2Jun-160
2Jul-160
2Aug-160
2Sep-160
2Oct-160
2Nov-160
2Dec-161
3Jan-800
3Feb-800
3Mar-800
3Apr-800
3May-800
3Jun-800
3Jul-800
3Aug-800
3Sep-800
3Oct-800
3Nov-802
3Dec-800
3Jan-810
3Feb-810
3Mar-810
3Apr-810
3May-810
3Jun-811
3Jul-810
3Aug-810
3Sep-811
3Oct-811
3Nov-810
3Dec-810
3Jan-820
3Feb-820
3Mar-820
3Apr-821
3May-820
3Jun-820
3Jul-822
3Aug-820
3Sep-820
3Oct-820
3Nov-820
3Dec-820
3Jan-830
3Feb-830
3Mar-830
3Apr-830
3May-830
3Jun-830
3Jul-830
3Aug-830
3Sep-830
3Oct-830
3Nov-830
3Dec-830
3Jan-840
3Feb-840
3Mar-840
3Apr-840
3May-840
3Jun-840
3Jul-840
3Aug-840
3Sep-840
3Oct-840
3Nov-840
3Dec-840
3Jan-850
3Feb-850
3Mar-850
3Apr-850
3May-850
3Jun-850
3Jul-850
3Aug-851
3Sep-850
3Oct-850
3Nov-850
3Dec-851
3Jan-861
3Feb-860
3Mar-860
3Apr-860
3May-860
3Jun-860
3Jul-860
3Aug-860
3Sep-860
3Oct-860
3Nov-860
3Dec-860
3Jan-870
3Feb-870
3Mar-870
3Apr-870
3May-870
3Jun-870
3Jul-870
3Aug-870
3Sep-870
3Oct-870
3Nov-870
3Dec-871
3Jan-880
3Feb-880
3Mar-880
3Apr-880
3May-880
3Jun-880
3Jul-880
3Aug-880
3Sep-880
3Oct-880
3Nov-880
3Dec-880
3Jan-890
3Feb-890
3Mar-890
3Apr-890
3May-890
3Jun-890
3Jul-890
3Aug-891
3Sep-890
3Oct-890
3Nov-890
3Dec-890
3Jan-900
3Feb-900
3Mar-900
3Apr-900
3May-900
3Jun-900
3Jul-900
3Aug-900
3Sep-900
3Oct-900
3Nov-900
3Dec-900
3Jan-910
3Feb-910
3Mar-910
3Apr-910
3May-910
3Jun-910
3Jul-910
3Aug-912
3Sep-910
3Oct-910
3Nov-910
3Dec-910
3Jan-920
3Feb-920
3Mar-920
3Apr-922
3May-920
3Jun-920
3Jul-920
3Aug-920
3Sep-920
3Oct-920
3Nov-920
3Dec-920
3Jan-930
3Feb-930
3Mar-930
3Apr-930
3May-930
3Jun-931
3Jul-930
3Aug-930
3Sep-930
3Oct-930
3Nov-930
3Dec-930
3Jan-940
3Feb-940
3Mar-940
3Apr-940
3May-940
3Jun-941
3Jul-940
3Aug-940
3Sep-940
3Oct-940
3Nov-940
3Dec-940
3Jan-950
3Feb-950
3Mar-950
3Apr-950
3May-950
3Jun-950
3Jul-950
3Aug-950
3Sep-950
3Oct-952
3Nov-950
3Dec-950
3Jan-960
3Feb-960
3Mar-960
3Apr-960
3May-961
3Jun-960
3Jul-960
3Aug-960
3Sep-960
3Oct-960
3Nov-960
3Dec-960
3Jan-972
3Feb-970
3Mar-970
3Apr-970
3May-970
3Jun-970
3Jul-970
3Aug-970
3Sep-970
3Oct-970
3Nov-970
3Dec-970
3Jan-980
3Feb-980
3Mar-981
3Apr-980
3May-980
3Jun-980
3Jul-980
3Aug-980
3Sep-980
3Oct-981
3Nov-980
3Dec-980
3Jan-990
3Feb-990
3Mar-990
3Apr-991
3May-990
3Jun-990
3Jul-990
3Aug-990
3Sep-990
3Oct-990
3Nov-990
3Dec-990
3Jan-000
3Feb-000
3Mar-000
3Apr-001
3May-000
3Jun-000
3Jul-000
3Aug-001
3Sep-000
3Oct-000
3Nov-000
3Dec-001
3Jan-010
3Feb-010
3Mar-010
3Apr-010
3May-010
3Jun-010
3Jul-010
3Aug-010
3Sep-010
3Oct-011
3Nov-011
3Dec-010
3Jan-020
3Feb-020
3Mar-020
3Apr-020
3May-020
3Jun-020
3Jul-020
3Aug-020
3Sep-020
3Oct-020
3Nov-021
3Dec-021
3Jan-031
3Feb-030
3Mar-030
3Apr-030
3May-030
3Jun-030
3Jul-030
3Aug-030
3Sep-030
3Oct-030
3Nov-030
3Dec-030
3Jan-040
3Feb-040
3Mar-040
3Apr-040
3May-040
3Jun-040
3Jul-040
3Aug-040
3Sep-040
3Oct-040
3Nov-041
3Dec-041
3Jan-050
3Feb-050
3Mar-050
3Apr-050
3May-051
3Jun-050
3Jul-050
3Aug-050
3Sep-050
3Oct-050
3Nov-051
3Dec-050
3Jan-060
3Feb-060
3Mar-060
3Apr-060
3May-060
3Jun-060
3Jul-060
3Aug-060
3Sep-060
3Oct-060
3Nov-060
3Dec-061
3Jan-070
3Feb-070
3Mar-070
3Apr-070
3May-071
3Jun-070
3Jul-070
3Aug-070
3Sep-070
3Oct-070
3Nov-070
3Dec-070
3Jan-080
3Feb-081
3Mar-080
3Apr-080
3May-080
3Jun-080
3Jul-080
3Aug-082
3Sep-080
3Oct-080
3Nov-080
3Dec-080
3Jan-091
3Feb-090
3Mar-090
3Apr-092
3May-090
3Jun-090
3Jul-090
3Aug-090
3Sep-090
3Oct-090
3Nov-090
3Dec-090
3Jan-100
3Feb-100
3Mar-100
3Apr-100
3May-101
3Jun-100
3Jul-101
3Aug-100
3Sep-100
3Oct-100
3Nov-100
3Dec-100
3Jan-110
3Feb-110
3Mar-110
3Apr-110
3May-110
3Jun-110
3Jul-110
3Aug-110
3Sep-110
3Oct-110
3Nov-110
3Dec-110
3Jan-120
3Feb-120
3Mar-120
3Apr-120
3May-120
3Jun-120
3Jul-120
3Aug-120
3Sep-120
3Oct-120
3Nov-120
3Dec-120
3Jan-130
3Feb-130
3Mar-130
3Apr-131
3May-130
3Jun-130
3Jul-130
3Aug-130
3Sep-130
3Oct-130
3Nov-130
3Dec-130
3Jan-140
3Feb-140
3Mar-140
3Apr-140
3May-141
3Jun-140
3Jul-140
3Aug-140
3Sep-140
3Oct-140
3Nov-140
3Dec-140
3Jan-151
3Feb-150
3Mar-150
3Apr-150
3May-150
3Jun-150
3Jul-150
3Aug-150
3Sep-150
3Oct-150
3Nov-150
3Dec-150
3Jan-160
3Feb-160
3Mar-160
3Apr-160
3May-160
3Jun-160
3Jul-160
3Aug-160
3Sep-160
3Oct-160
3Nov-160
3Dec-160
9 REPLIES 9
Reeza
Super User

Didn't someone provide a solution in your last post? Did that not work for you and if not, why not?

Did you review David Cassell's paper?

 

http://www2.sas.com/proceedings/forum2007/183-2007.pdf

 

 

nazmul
Quartz | Level 8

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;

 

 

 

 

 

 

 

 

 

 

 

 

Ksharp
Super User
I have no time to go through all this.
Assuming in an industry XX , there are 25% is 0 , 25% is 1 , 25% is 2 , 25% is 3  in the original count variable.



data want;
 array x{4} _temporary_ (0 1 2 3);
 do i=1 to 1000;
  count=x{rand('table',0.25,0.25,0.25,0.25)};
  output;
 end;
run;

proc freq data=want;
table count/nopercent norow nocol;
run;

nazmul
Quartz | Level 8

Dear Everyone,

 

I contacted with SAS support team and they said the simulation I am trying to do cannot be done in SAS with any specific command. Thank you for trying to help me. 

Reeza
Super User

@nazmul wrote:

Dear Everyone,

 

I contacted with SAS support team and they said the simulation I am trying to do cannot be done in SAS with any specific command. Thank you for trying to help me. 


This means you need to write the code yourself, that there's no prepackaged routine to do it for you. This is common when you need to simulate your own processes. If you're going to be simulating data it's probably worth reading Rick Wicklins book on simulating data in SAS or his blog as a starter. 

nazmul
Quartz | Level 8

Thank you Ms. Reeza. I will read the book.

TomKari
Onyx | Level 15

Questions on this community tend to be of two types. One is that people ask for advice on how to approach a problem, from a methdological or design perspective. Another is that they're having trouble getting a piece of code working, and they need advice on why it's failing.

 

On the other hand, you're asking the volunteers on this community to develop a somewhat complex piece of software for you, which would include reviewing in detail the notes you have provided, and probably corresponding back and forth to clear up ambiguity and to ensure complete understanding.

 

This would almost certainly be several days of work, which goes beyond what this community is designed for.

 

If you continue to be stuck, I suggest that your company engage an expert SAS consultant, such as myself, to do those several days of work on a paid basis. That's probably the fastest route to getting the results you need.

 

Tom

nazmul
Quartz | Level 8

Thank you. I just dropped the variable from my dataset. Actually it was not the main variable in my research. I am a FInance PhD student and I do research for my academic work. So, as a student it is not possible for me to finance any paid work right now. Thank you for your suggestion.

Reeza
Super User
This is definitely a thesis level project - that you should do on your own IMO. You'll learn a lot and you'll understand it. It really isn't that difficult, but to be honest, you haven't clarified the rules and you've posted the entire gamut at once. Break the problem down to smaller pieces and solve each one individually. Then put it all together.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1317 views
  • 10 likes
  • 4 in conversation