Contributor
Posts: 73

# simulated distributions

[ Edited ]

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.

 Aindus48 date count 1 Jan-80 0 1 Feb-80 0 1 Mar-80 0 1 Apr-80 0 1 May-80 0 1 Jun-80 0 1 Jul-80 0 1 Aug-80 0 1 Sep-80 0 1 Oct-80 0 1 Nov-80 0 1 Dec-80 0 1 Jan-81 0 1 Feb-81 0 1 Mar-81 0 1 Apr-81 0 1 May-81 1 1 Jun-81 0 1 Jul-81 0 1 Aug-81 0 1 Sep-81 0 1 Oct-81 0 1 Nov-81 0 1 Dec-81 0 1 Jan-82 0 1 Feb-82 0 1 Mar-82 0 1 Apr-82 1 1 May-82 0 1 Jun-82 0 1 Jul-82 0 1 Aug-82 0 1 Sep-82 0 1 Oct-82 0 1 Nov-82 0 1 Dec-82 0 1 Jan-83 0 1 Feb-83 0 1 Mar-83 0 1 Apr-83 0 1 May-83 0 1 Jun-83 0 1 Jul-83 0 1 Aug-83 0 1 Sep-83 1 1 Oct-83 0 1 Nov-83 0 1 Dec-83 0 1 Jan-84 0 1 Feb-84 0 1 Mar-84 0 1 Apr-84 0 1 May-84 1 1 Jun-84 0 1 Jul-84 0 1 Aug-84 0 1 Sep-84 0 1 Oct-84 0 1 Nov-84 0 1 Dec-84 1 1 Jan-85 0 1 Feb-85 1 1 Mar-85 0 1 Apr-85 0 1 May-85 0 1 Jun-85 0 1 Jul-85 0 1 Aug-85 0 1 Sep-85 0 1 Oct-85 0 1 Nov-85 0 1 Dec-85 0 1 Jan-86 0 1 Feb-86 0 1 Mar-86 0 1 Apr-86 0 1 May-86 0 1 Jun-86 0 1 Jul-86 0 1 Aug-86 0 1 Sep-86 0 1 Oct-86 0 1 Nov-86 0 1 Dec-86 0 1 Jan-87 0 1 Feb-87 0 1 Mar-87 0 1 Apr-87 0 1 May-87 0 1 Jun-87 0 1 Jul-87 0 1 Aug-87 0 1 Sep-87 0 1 Oct-87 0 1 Nov-87 0 1 Dec-87 0 1 Jan-88 0 1 Feb-88 0 1 Mar-88 0 1 Apr-88 0 1 May-88 0 1 Jun-88 0 1 Jul-88 0 1 Aug-88 0 1 Sep-88 0 1 Oct-88 0 1 Nov-88 0 1 Dec-88 0 1 Jan-89 0 1 Feb-89 1 1 Mar-89 1 1 Apr-89 0 1 May-89 0 1 Jun-89 0 1 Jul-89 0 1 Aug-89 0 1 Sep-89 0 1 Oct-89 0 1 Nov-89 0 1 Dec-89 0 1 Jan-90 1 1 Feb-90 0 1 Mar-90 0 1 Apr-90 0 1 May-90 0 1 Jun-90 0 1 Jul-90 0 1 Aug-90 0 1 Sep-90 0 1 Oct-90 0 1 Nov-90 0 1 Dec-90 0 1 Jan-91 0 1 Feb-91 0 1 Mar-91 0 1 Apr-91 0 1 May-91 0 1 Jun-91 0 1 Jul-91 1 1 Aug-91 1 1 Sep-91 0 1 Oct-91 1 1 Nov-91 0 1 Dec-91 0 1 Jan-92 0 1 Feb-92 1 1 Mar-92 0 1 Apr-92 0 1 May-92 0 1 Jun-92 0 1 Jul-92 0 1 Aug-92 0 1 Sep-92 0 1 Oct-92 1 1 Nov-92 0 1 Dec-92 0 1 Jan-93 0 1 Feb-93 0 1 Mar-93 0 1 Apr-93 0 1 May-93 0 1 Jun-93 0 1 Jul-93 0 1 Aug-93 0 1 Sep-93 0 1 Oct-93 0 1 Nov-93 0 1 Dec-93 0 1 Jan-94 0 1 Feb-94 0 1 Mar-94 0 1 Apr-94 0 1 May-94 0 1 Jun-94 0 1 Jul-94 0 1 Aug-94 0 1 Sep-94 0 1 Oct-94 0 1 Nov-94 0 1 Dec-94 0 1 Jan-95 0 1 Feb-95 1 1 Mar-95 0 1 Apr-95 0 1 May-95 0 1 Jun-95 0 1 Jul-95 0 1 Aug-95 0 1 Sep-95 0 1 Oct-95 0 1 Nov-95 0 1 Dec-95 0 1 Jan-96 0 1 Feb-96 2 1 Mar-96 2 1 Apr-96 0 1 May-96 2 1 Jun-96 0 1 Jul-96 0 1 Aug-96 0 1 Sep-96 1 1 Oct-96 0 1 Nov-96 0 1 Dec-96 0 1 Jan-97 0 1 Feb-97 0 1 Mar-97 0 1 Apr-97 0 1 May-97 0 1 Jun-97 0 1 Jul-97 0 1 Aug-97 0 1 Sep-97 0 1 Oct-97 1 1 Nov-97 0 1 Dec-97 0 1 Jan-98 0 1 Feb-98 0 1 Mar-98 0 1 Apr-98 0 1 May-98 1 1 Jun-98 0 1 Jul-98 1 1 Aug-98 0 1 Sep-98 0 1 Oct-98 0 1 Nov-98 1 1 Dec-98 0 1 Jan-99 0 1 Feb-99 0 1 Mar-99 2 1 Apr-99 1 1 May-99 0 1 Jun-99 0 1 Jul-99 0 1 Aug-99 0 1 Sep-99 0 1 Oct-99 0 1 Nov-99 0 1 Dec-99 0 1 Jan-00 0 1 Feb-00 0 1 Mar-00 0 1 Apr-00 0 1 May-00 0 1 Jun-00 0 1 Jul-00 0 1 Aug-00 0 1 Sep-00 0 1 Oct-00 0 1 Nov-00 0 1 Dec-00 0 1 Jan-01 0 1 Feb-01 0 1 Mar-01 0 1 Apr-01 0 1 May-01 0 1 Jun-01 0 1 Jul-01 0 1 Aug-01 0 1 Sep-01 0 1 Oct-01 0 1 Nov-01 0 1 Dec-01 0 1 Jan-02 0 1 Feb-02 0 1 Mar-02 0 1 Apr-02 0 1 May-02 0 1 Jun-02 0 1 Jul-02 0 1 Aug-02 0 1 Sep-02 0 1 Oct-02 0 1 Nov-02 0 1 Dec-02 0 1 Jan-03 0 1 Feb-03 0 1 Mar-03 0 1 Apr-03 0 1 May-03 0 1 Jun-03 0 1 Jul-03 0 1 Aug-03 0 1 Sep-03 0 1 Oct-03 0 1 Nov-03 0 1 Dec-03 0 1 Jan-04 0 1 Feb-04 0 1 Mar-04 1 1 Apr-04 0 1 May-04 1 1 Jun-04 0 1 Jul-04 1 1 Aug-04 1 1 Sep-04 0 1 Oct-04 1 1 Nov-04 0 1 Dec-04 0 1 Jan-05 0 1 Feb-05 0 1 Mar-05 0 1 Apr-05 0 1 May-05 1 1 Jun-05 0 1 Jul-05 1 1 Aug-05 0 1 Sep-05 0 1 Oct-05 0 1 Nov-05 0 1 Dec-05 0 1 Jan-06 0 1 Feb-06 0 1 Mar-06 0 1 Apr-06 0 1 May-06 0 1 Jun-06 0 1 Jul-06 0 1 Aug-06 0 1 Sep-06 0 1 Oct-06 0 1 Nov-06 0 1 Dec-06 1 1 Jan-07 0 1 Feb-07 0 1 Mar-07 0 1 Apr-07 0 1 May-07 1 1 Jun-07 0 1 Jul-07 0 1 Aug-07 0 1 Sep-07 0 1 Oct-07 1 1 Nov-07 0 1 Dec-07 0 1 Jan-08 0 1 Feb-08 0 1 Mar-08 0 1 Apr-08 0 1 May-08 0 1 Jun-08 0 1 Jul-08 0 1 Aug-08 0 1 Sep-08 0 1 Oct-08 0 1 Nov-08 0 1 Dec-08 0 1 Jan-09 0 1 Feb-09 1 1 Mar-09 0 1 Apr-09 1 1 May-09 0 1 Jun-09 0 1 Jul-09 2 1 Aug-09 0 1 Sep-09 0 1 Oct-09 0 1 Nov-09 0 1 Dec-09 0 1 Jan-10 0 1 Feb-10 0 1 Mar-10 0 1 Apr-10 0 1 May-10 0 1 Jun-10 1 1 Jul-10 0 1 Aug-10 0 1 Sep-10 0 1 Oct-10 0 1 Nov-10 0 1 Dec-10 0 1 Jan-11 0 1 Feb-11 0 1 Mar-11 0 1 Apr-11 0 1 May-11 0 1 Jun-11 1 1 Jul-11 0 1 Aug-11 1 1 Sep-11 0 1 Oct-11 1 1 Nov-11 1 1 Dec-11 0 1 Jan-12 0 1 Feb-12 0 1 Mar-12 0 1 Apr-12 0 1 May-12 0 1 Jun-12 0 1 Jul-12 0 1 Aug-12 0 1 Sep-12 0 1 Oct-12 0 1 Nov-12 0 1 Dec-12 0 1 Jan-13 0 1 Feb-13 0 1 Mar-13 0 1 Apr-13 0 1 May-13 0 1 Jun-13 0 1 Jul-13 0 1 Aug-13 0 1 Sep-13 1 1 Oct-13 0 1 Nov-13 0 1 Dec-13 0 1 Jan-14 0 1 Feb-14 0 1 Mar-14 0 1 Apr-14 0 1 May-14 0 1 Jun-14 0 1 Jul-14 0 1 Aug-14 0 1 Sep-14 0 1 Oct-14 0 1 Nov-14 0 1 Dec-14 0 1 Jan-15 0 1 Feb-15 0 1 Mar-15 0 1 Apr-15 0 1 May-15 0 1 Jun-15 0 1 Jul-15 0 1 Aug-15 0 1 Sep-15 0 1 Oct-15 0 1 Nov-15 0 1 Dec-15 0 1 Jan-16 0 1 Feb-16 0 1 Mar-16 0 1 Apr-16 0 1 May-16 0 1 Jun-16 0 1 Jul-16 0 1 Aug-16 0 1 Sep-16 0 1 Oct-16 0 1 Nov-16 0 1 Dec-16 0 2 Jan-80 0 2 Feb-80 0 2 Mar-80 0 2 Apr-80 0 2 May-80 0 2 Jun-80 0 2 Jul-80 0 2 Aug-80 0 2 Sep-80 0 2 Oct-80 1 2 Nov-80 0 2 Dec-80 0 2 Jan-81 0 2 Feb-81 4 2 Mar-81 0 2 Apr-81 0 2 May-81 0 2 Jun-81 0 2 Jul-81 1 2 Aug-81 0 2 Sep-81 0 2 Oct-81 1 2 Nov-81 0 2 Dec-81 0 2 Jan-82 1 2 Feb-82 0 2 Mar-82 0 2 Apr-82 1 2 May-82 0 2 Jun-82 0 2 Jul-82 1 2 Aug-82 0 2 Sep-82 0 2 Oct-82 0 2 Nov-82 0 2 Dec-82 1 2 Jan-83 0 2 Feb-83 0 2 Mar-83 0 2 Apr-83 0 2 May-83 0 2 Jun-83 1 2 Jul-83 1 2 Aug-83 1 2 Sep-83 1 2 Oct-83 1 2 Nov-83 0 2 Dec-83 2 2 Jan-84 1 2 Feb-84 0 2 Mar-84 1 2 Apr-84 3 2 May-84 1 2 Jun-84 1 2 Jul-84 3 2 Aug-84 0 2 Sep-84 1 2 Oct-84 2 2 Nov-84 0 2 Dec-84 1 2 Jan-85 0 2 Feb-85 0 2 Mar-85 0 2 Apr-85 0 2 May-85 0 2 Jun-85 0 2 Jul-85 0 2 Aug-85 0 2 Sep-85 0 2 Oct-85 1 2 Nov-85 0 2 Dec-85 0 2 Jan-86 1 2 Feb-86 1 2 Mar-86 1 2 Apr-86 0 2 May-86 0 2 Jun-86 1 2 Jul-86 0 2 Aug-86 0 2 Sep-86 2 2 Oct-86 0 2 Nov-86 0 2 Dec-86 0 2 Jan-87 0 2 Feb-87 0 2 Mar-87 2 2 Apr-87 1 2 May-87 0 2 Jun-87 2 2 Jul-87 0 2 Aug-87 0 2 Sep-87 1 2 Oct-87 0 2 Nov-87 0 2 Dec-87 0 2 Jan-88 0 2 Feb-88 2 2 Mar-88 1 2 Apr-88 0 2 May-88 0 2 Jun-88 0 2 Jul-88 0 2 Aug-88 0 2 Sep-88 0 2 Oct-88 1 2 Nov-88 0 2 Dec-88 1 2 Jan-89 1 2 Feb-89 2 2 Mar-89 0 2 Apr-89 0 2 May-89 0 2 Jun-89 1 2 Jul-89 0 2 Aug-89 0 2 Sep-89 0 2 Oct-89 0 2 Nov-89 1 2 Dec-89 1 2 Jan-90 1 2 Feb-90 2 2 Mar-90 0 2 Apr-90 0 2 May-90 0 2 Jun-90 0 2 Jul-90 0 2 Aug-90 1 2 Sep-90 0 2 Oct-90 0 2 Nov-90 0 2 Dec-90 0 2 Jan-91 0 2 Feb-91 0 2 Mar-91 0 2 Apr-91 2 2 May-91 2 2 Jun-91 0 2 Jul-91 0 2 Aug-91 0 2 Sep-91 1 2 Oct-91 3 2 Nov-91 0 2 Dec-91 1 2 Jan-92 1 2 Feb-92 0 2 Mar-92 0 2 Apr-92 0 2 May-92 0 2 Jun-92 2 2 Jul-92 0 2 Aug-92 0 2 Sep-92 2 2 Oct-92 2 2 Nov-92 0 2 Dec-92 0 2 Jan-93 0 2 Feb-93 3 2 Mar-93 0 2 Apr-93 0 2 May-93 0 2 Jun-93 0 2 Jul-93 1 2 Aug-93 1 2 Sep-93 0 2 Oct-93 0 2 Nov-93 0 2 Dec-93 1 2 Jan-94 0 2 Feb-94 1 2 Mar-94 0 2 Apr-94 0 2 May-94 0 2 Jun-94 1 2 Jul-94 0 2 Aug-94 1 2 Sep-94 0 2 Oct-94 1 2 Nov-94 2 2 Dec-94 1 2 Jan-95 0 2 Feb-95 0 2 Mar-95 1 2 Apr-95 1 2 May-95 0 2 Jun-95 0 2 Jul-95 0 2 Aug-95 0 2 Sep-95 0 2 Oct-95 0 2 Nov-95 2 2 Dec-95 2 2 Jan-96 0 2 Feb-96 0 2 Mar-96 0 2 Apr-96 0 2 May-96 4 2 Jun-96 0 2 Jul-96 0 2 Aug-96 0 2 Sep-96 1 2 Oct-96 1 2 Nov-96 0 2 Dec-96 0 2 Jan-97 0 2 Feb-97 1 2 Mar-97 1 2 Apr-97 0 2 May-97 0 2 Jun-97 0 2 Jul-97 1 2 Aug-97 1 2 Sep-97 3 2 Oct-97 0 2 Nov-97 0 2 Dec-97 1 2 Jan-98 4 2 Feb-98 1 2 Mar-98 1 2 Apr-98 0 2 May-98 0 2 Jun-98 0 2 Jul-98 0 2 Aug-98 1 2 Sep-98 0 2 Oct-98 0 2 Nov-98 0 2 Dec-98 0 2 Jan-99 0 2 Feb-99 4 2 Mar-99 0 2 Apr-99 1 2 May-99 0 2 Jun-99 0 2 Jul-99 0 2 Aug-99 0 2 Sep-99 0 2 Oct-99 1 2 Nov-99 0 2 Dec-99 1 2 Jan-00 1 2 Feb-00 1 2 Mar-00 0 2 Apr-00 0 2 May-00 0 2 Jun-00 1 2 Jul-00 1 2 Aug-00 1 2 Sep-00 1 2 Oct-00 1 2 Nov-00 0 2 Dec-00 1 2 Jan-01 0 2 Feb-01 0 2 Mar-01 0 2 Apr-01 0 2 May-01 1 2 Jun-01 0 2 Jul-01 1 2 Aug-01 0 2 Sep-01 2 2 Oct-01 1 2 Nov-01 0 2 Dec-01 0 2 Jan-02 0 2 Feb-02 1 2 Mar-02 0 2 Apr-02 0 2 May-02 2 2 Jun-02 1 2 Jul-02 1 2 Aug-02 0 2 Sep-02 0 2 Oct-02 0 2 Nov-02 0 2 Dec-02 1 2 Jan-03 0 2 Feb-03 0 2 Mar-03 1 2 Apr-03 0 2 May-03 0 2 Jun-03 3 2 Jul-03 0 2 Aug-03 0 2 Sep-03 0 2 Oct-03 0 2 Nov-03 1 2 Dec-03 0 2 Jan-04 0 2 Feb-04 0 2 Mar-04 1 2 Apr-04 1 2 May-04 2 2 Jun-04 1 2 Jul-04 1 2 Aug-04 1 2 Sep-04 0 2 Oct-04 0 2 Nov-04 0 2 Dec-04 1 2 Jan-05 0 2 Feb-05 0 2 Mar-05 1 2 Apr-05 1 2 May-05 0 2 Jun-05 0 2 Jul-05 0 2 Aug-05 1 2 Sep-05 0 2 Oct-05 0 2 Nov-05 0 2 Dec-05 0 2 Jan-06 0 2 Feb-06 1 2 Mar-06 1 2 Apr-06 0 2 May-06 2 2 Jun-06 0 2 Jul-06 1 2 Aug-06 1 2 Sep-06 3 2 Oct-06 2 2 Nov-06 0 2 Dec-06 0 2 Jan-07 0 2 Feb-07 0 2 Mar-07 0 2 Apr-07 2 2 May-07 0 2 Jun-07 1 2 Jul-07 0 2 Aug-07 1 2 Sep-07 1 2 Oct-07 1 2 Nov-07 2 2 Dec-07 1 2 Jan-08 0 2 Feb-08 1 2 Mar-08 0 2 Apr-08 0 2 May-08 0 2 Jun-08 4 2 Jul-08 0 2 Aug-08 0 2 Sep-08 0 2 Oct-08 0 2 Nov-08 0 2 Dec-08 0 2 Jan-09 0 2 Feb-09 0 2 Mar-09 0 2 Apr-09 1 2 May-09 0 2 Jun-09 0 2 Jul-09 1 2 Aug-09 0 2 Sep-09 0 2 Oct-09 0 2 Nov-09 1 2 Dec-09 0 2 Jan-10 0 2 Feb-10 0 2 Mar-10 0 2 Apr-10 0 2 May-10 0 2 Jun-10 2 2 Jul-10 1 2 Aug-10 0 2 Sep-10 1 2 Oct-10 0 2 Nov-10 1 2 Dec-10 0 2 Jan-11 1 2 Feb-11 0 2 Mar-11 0 2 Apr-11 0 2 May-11 0 2 Jun-11 1 2 Jul-11 0 2 Aug-11 1 2 Sep-11 1 2 Oct-11 0 2 Nov-11 0 2 Dec-11 0 2 Jan-12 0 2 Feb-12 0 2 Mar-12 0 2 Apr-12 0 2 May-12 1 2 Jun-12 0 2 Jul-12 0 2 Aug-12 0 2 Sep-12 0 2 Oct-12 0 2 Nov-12 1 2 Dec-12 0 2 Jan-13 0 2 Feb-13 1 2 Mar-13 0 2 Apr-13 0 2 May-13 0 2 Jun-13 0 2 Jul-13 1 2 Aug-13 0 2 Sep-13 2 2 Oct-13 0 2 Nov-13 0 2 Dec-13 0 2 Jan-14 0 2 Feb-14 0 2 Mar-14 0 2 Apr-14 1 2 May-14 2 2 Jun-14 1 2 Jul-14 1 2 Aug-14 1 2 Sep-14 1 2 Oct-14 1 2 Nov-14 0 2 Dec-14 0 2 Jan-15 0 2 Feb-15 0 2 Mar-15 2 2 Apr-15 0 2 May-15 0 2 Jun-15 1 2 Jul-15 0 2 Aug-15 0 2 Sep-15 0 2 Oct-15 1 2 Nov-15 2 2 Dec-15 0 2 Jan-16 0 2 Feb-16 0 2 Mar-16 0 2 Apr-16 0 2 May-16 0 2 Jun-16 0 2 Jul-16 0 2 Aug-16 0 2 Sep-16 0 2 Oct-16 0 2 Nov-16 0 2 Dec-16 1 3 Jan-80 0 3 Feb-80 0 3 Mar-80 0 3 Apr-80 0 3 May-80 0 3 Jun-80 0 3 Jul-80 0 3 Aug-80 0 3 Sep-80 0 3 Oct-80 0 3 Nov-80 2 3 Dec-80 0 3 Jan-81 0 3 Feb-81 0 3 Mar-81 0 3 Apr-81 0 3 May-81 0 3 Jun-81 1 3 Jul-81 0 3 Aug-81 0 3 Sep-81 1 3 Oct-81 1 3 Nov-81 0 3 Dec-81 0 3 Jan-82 0 3 Feb-82 0 3 Mar-82 0 3 Apr-82 1 3 May-82 0 3 Jun-82 0 3 Jul-82 2 3 Aug-82 0 3 Sep-82 0 3 Oct-82 0 3 Nov-82 0 3 Dec-82 0 3 Jan-83 0 3 Feb-83 0 3 Mar-83 0 3 Apr-83 0 3 May-83 0 3 Jun-83 0 3 Jul-83 0 3 Aug-83 0 3 Sep-83 0 3 Oct-83 0 3 Nov-83 0 3 Dec-83 0 3 Jan-84 0 3 Feb-84 0 3 Mar-84 0 3 Apr-84 0 3 May-84 0 3 Jun-84 0 3 Jul-84 0 3 Aug-84 0 3 Sep-84 0 3 Oct-84 0 3 Nov-84 0 3 Dec-84 0 3 Jan-85 0 3 Feb-85 0 3 Mar-85 0 3 Apr-85 0 3 May-85 0 3 Jun-85 0 3 Jul-85 0 3 Aug-85 1 3 Sep-85 0 3 Oct-85 0 3 Nov-85 0 3 Dec-85 1 3 Jan-86 1 3 Feb-86 0 3 Mar-86 0 3 Apr-86 0 3 May-86 0 3 Jun-86 0 3 Jul-86 0 3 Aug-86 0 3 Sep-86 0 3 Oct-86 0 3 Nov-86 0 3 Dec-86 0 3 Jan-87 0 3 Feb-87 0 3 Mar-87 0 3 Apr-87 0 3 May-87 0 3 Jun-87 0 3 Jul-87 0 3 Aug-87 0 3 Sep-87 0 3 Oct-87 0 3 Nov-87 0 3 Dec-87 1 3 Jan-88 0 3 Feb-88 0 3 Mar-88 0 3 Apr-88 0 3 May-88 0 3 Jun-88 0 3 Jul-88 0 3 Aug-88 0 3 Sep-88 0 3 Oct-88 0 3 Nov-88 0 3 Dec-88 0 3 Jan-89 0 3 Feb-89 0 3 Mar-89 0 3 Apr-89 0 3 May-89 0 3 Jun-89 0 3 Jul-89 0 3 Aug-89 1 3 Sep-89 0 3 Oct-89 0 3 Nov-89 0 3 Dec-89 0 3 Jan-90 0 3 Feb-90 0 3 Mar-90 0 3 Apr-90 0 3 May-90 0 3 Jun-90 0 3 Jul-90 0 3 Aug-90 0 3 Sep-90 0 3 Oct-90 0 3 Nov-90 0 3 Dec-90 0 3 Jan-91 0 3 Feb-91 0 3 Mar-91 0 3 Apr-91 0 3 May-91 0 3 Jun-91 0 3 Jul-91 0 3 Aug-91 2 3 Sep-91 0 3 Oct-91 0 3 Nov-91 0 3 Dec-91 0 3 Jan-92 0 3 Feb-92 0 3 Mar-92 0 3 Apr-92 2 3 May-92 0 3 Jun-92 0 3 Jul-92 0 3 Aug-92 0 3 Sep-92 0 3 Oct-92 0 3 Nov-92 0 3 Dec-92 0 3 Jan-93 0 3 Feb-93 0 3 Mar-93 0 3 Apr-93 0 3 May-93 0 3 Jun-93 1 3 Jul-93 0 3 Aug-93 0 3 Sep-93 0 3 Oct-93 0 3 Nov-93 0 3 Dec-93 0 3 Jan-94 0 3 Feb-94 0 3 Mar-94 0 3 Apr-94 0 3 May-94 0 3 Jun-94 1 3 Jul-94 0 3 Aug-94 0 3 Sep-94 0 3 Oct-94 0 3 Nov-94 0 3 Dec-94 0 3 Jan-95 0 3 Feb-95 0 3 Mar-95 0 3 Apr-95 0 3 May-95 0 3 Jun-95 0 3 Jul-95 0 3 Aug-95 0 3 Sep-95 0 3 Oct-95 2 3 Nov-95 0 3 Dec-95 0 3 Jan-96 0 3 Feb-96 0 3 Mar-96 0 3 Apr-96 0 3 May-96 1 3 Jun-96 0 3 Jul-96 0 3 Aug-96 0 3 Sep-96 0 3 Oct-96 0 3 Nov-96 0 3 Dec-96 0 3 Jan-97 2 3 Feb-97 0 3 Mar-97 0 3 Apr-97 0 3 May-97 0 3 Jun-97 0 3 Jul-97 0 3 Aug-97 0 3 Sep-97 0 3 Oct-97 0 3 Nov-97 0 3 Dec-97 0 3 Jan-98 0 3 Feb-98 0 3 Mar-98 1 3 Apr-98 0 3 May-98 0 3 Jun-98 0 3 Jul-98 0 3 Aug-98 0 3 Sep-98 0 3 Oct-98 1 3 Nov-98 0 3 Dec-98 0 3 Jan-99 0 3 Feb-99 0 3 Mar-99 0 3 Apr-99 1 3 May-99 0 3 Jun-99 0 3 Jul-99 0 3 Aug-99 0 3 Sep-99 0 3 Oct-99 0 3 Nov-99 0 3 Dec-99 0 3 Jan-00 0 3 Feb-00 0 3 Mar-00 0 3 Apr-00 1 3 May-00 0 3 Jun-00 0 3 Jul-00 0 3 Aug-00 1 3 Sep-00 0 3 Oct-00 0 3 Nov-00 0 3 Dec-00 1 3 Jan-01 0 3 Feb-01 0 3 Mar-01 0 3 Apr-01 0 3 May-01 0 3 Jun-01 0 3 Jul-01 0 3 Aug-01 0 3 Sep-01 0 3 Oct-01 1 3 Nov-01 1 3 Dec-01 0 3 Jan-02 0 3 Feb-02 0 3 Mar-02 0 3 Apr-02 0 3 May-02 0 3 Jun-02 0 3 Jul-02 0 3 Aug-02 0 3 Sep-02 0 3 Oct-02 0 3 Nov-02 1 3 Dec-02 1 3 Jan-03 1 3 Feb-03 0 3 Mar-03 0 3 Apr-03 0 3 May-03 0 3 Jun-03 0 3 Jul-03 0 3 Aug-03 0 3 Sep-03 0 3 Oct-03 0 3 Nov-03 0 3 Dec-03 0 3 Jan-04 0 3 Feb-04 0 3 Mar-04 0 3 Apr-04 0 3 May-04 0 3 Jun-04 0 3 Jul-04 0 3 Aug-04 0 3 Sep-04 0 3 Oct-04 0 3 Nov-04 1 3 Dec-04 1 3 Jan-05 0 3 Feb-05 0 3 Mar-05 0 3 Apr-05 0 3 May-05 1 3 Jun-05 0 3 Jul-05 0 3 Aug-05 0 3 Sep-05 0 3 Oct-05 0 3 Nov-05 1 3 Dec-05 0 3 Jan-06 0 3 Feb-06 0 3 Mar-06 0 3 Apr-06 0 3 May-06 0 3 Jun-06 0 3 Jul-06 0 3 Aug-06 0 3 Sep-06 0 3 Oct-06 0 3 Nov-06 0 3 Dec-06 1 3 Jan-07 0 3 Feb-07 0 3 Mar-07 0 3 Apr-07 0 3 May-07 1 3 Jun-07 0 3 Jul-07 0 3 Aug-07 0 3 Sep-07 0 3 Oct-07 0 3 Nov-07 0 3 Dec-07 0 3 Jan-08 0 3 Feb-08 1 3 Mar-08 0 3 Apr-08 0 3 May-08 0 3 Jun-08 0 3 Jul-08 0 3 Aug-08 2 3 Sep-08 0 3 Oct-08 0 3 Nov-08 0 3 Dec-08 0 3 Jan-09 1 3 Feb-09 0 3 Mar-09 0 3 Apr-09 2 3 May-09 0 3 Jun-09 0 3 Jul-09 0 3 Aug-09 0 3 Sep-09 0 3 Oct-09 0 3 Nov-09 0 3 Dec-09 0 3 Jan-10 0 3 Feb-10 0 3 Mar-10 0 3 Apr-10 0 3 May-10 1 3 Jun-10 0 3 Jul-10 1 3 Aug-10 0 3 Sep-10 0 3 Oct-10 0 3 Nov-10 0 3 Dec-10 0 3 Jan-11 0 3 Feb-11 0 3 Mar-11 0 3 Apr-11 0 3 May-11 0 3 Jun-11 0 3 Jul-11 0 3 Aug-11 0 3 Sep-11 0 3 Oct-11 0 3 Nov-11 0 3 Dec-11 0 3 Jan-12 0 3 Feb-12 0 3 Mar-12 0 3 Apr-12 0 3 May-12 0 3 Jun-12 0 3 Jul-12 0 3 Aug-12 0 3 Sep-12 0 3 Oct-12 0 3 Nov-12 0 3 Dec-12 0 3 Jan-13 0 3 Feb-13 0 3 Mar-13 0 3 Apr-13 1 3 May-13 0 3 Jun-13 0 3 Jul-13 0 3 Aug-13 0 3 Sep-13 0 3 Oct-13 0 3 Nov-13 0 3 Dec-13 0 3 Jan-14 0 3 Feb-14 0 3 Mar-14 0 3 Apr-14 0 3 May-14 1 3 Jun-14 0 3 Jul-14 0 3 Aug-14 0 3 Sep-14 0 3 Oct-14 0 3 Nov-14 0 3 Dec-14 0 3 Jan-15 1 3 Feb-15 0 3 Mar-15 0 3 Apr-15 0 3 May-15 0 3 Jun-15 0 3 Jul-15 0 3 Aug-15 0 3 Sep-15 0 3 Oct-15 0 3 Nov-15 0 3 Dec-15 0 3 Jan-16 0 3 Feb-16 0 3 Mar-16 0 3 Apr-16 0 3 May-16 0 3 Jun-16 0 3 Jul-16 0 3 Aug-16 0 3 Sep-16 0 3 Oct-16 0 3 Nov-16 0 3 Dec-16 0
Super User
Posts: 23,224

## Re: simulated distributions

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

Contributor
Posts: 73

## Re: simulated distributions

[ Edited ]

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;``````

Super User
Posts: 10,679

## Re: simulated distributions

```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;

```
Contributor
Posts: 73

## Re: simulated distributions

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.

Super User
Posts: 23,224

## Re: simulated distributions

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.

Contributor
Posts: 73

## Re: simulated distributions

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

PROC Star
Posts: 1,286

## Re: simulated distributions

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

Contributor
Posts: 73

## Re: simulated distributions

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.

Super User
Posts: 23,224

## Re: simulated distributions

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.
Discussion stats
• 9 replies
• 341 views
• 10 likes
• 4 in conversation