Hi All,

I want to create a 3 X 3 matrix from 9 combinations of count variables Sev and Rch. The three different kinds of Sev are LoSev MedSev HiSev and similarly, three different levels of Rch is LoRch MedRch and HiRch. The total of three levels of Sev and Rch respectively is same, which is totalSevRch. So, I need to break the total counts of totalsevRch into 9 combinations of -
LoSev and LoRch, LoSev and MedRch, LoSev and HiRch, MedSev and LoRch and so on, by firm_id and year.

Kindly find the sample of the dataset here.


----------------------- copy starting from the next line -----------------------
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(Firm_id year LoSev MedSev HiSev LoRch MedRch HiRch) double totalSevRch
 1 2007  0  0 0  0  0  0  0
 1 2008  0  0 0  0  0  0  0
 1 2009  0  0 0  0  0  0  0
 1 2010  2  0 0  0  2  0  2
 1 2011  0  2 0  0  2  0  2
 1 2012 12 12 0  0 22  2 24
 1 2013  4  0 0  0  4  0  4
 1 2014  0 24 0 19  5  0 24
 1 2015  2  0 0  2  0  0  2
 1 2016  0  0 0  0  0  0  0
 2 2007  0  0 0  0  0  0  0
 2 2008  0  0 0  0  0  0  0
 2 2009  0  0 0  0  0  0  0
 2 2010  0  0 0  0  0  0  0
 2 2011  0  0 0  0  0  0  0
 2 2012  0  0 0  0  0  0  0
 2 2013  0  0 0  0  0  0  0
 2 2014  0  0 0  0  0  0  0
 2 2015  0  0 0  0  0  0  0
 2 2016  0  0 0  0  0  0  0
 4 2007  0  0 0  0  0  0  0
 4 2008  0  0 0  0  0  0  0
 4 2009  0  0 0  0  0  0  0
 4 2010  0  0 0  0  0  0  0
 4 2011  0  2 0  0  2  0  2
 4 2012  0  0 0  0  0  0  0
 4 2013  0  0 0  0  0  0  0
 4 2014  0  0 0  0  0  0  0
 4 2015  0  0 0  0  0  0  0
 4 2016  0  0 0  0  0  0  0
 5 2007  0  0 0  0  0  0  0
 5 2008  0  0 0  0  0  0  0
 5 2009  0  0 0  0  0  0  0
 5 2010  0  0 0  0  0  0  0
 5 2011  0  0 0  0  0  0  0
 5 2012  0  0 0  0  0  0  0
 5 2013  1  0 0  0  1  0  1
 5 2014  0  0 0  0  0  0  0
 5 2015  0  0 0  0  0  0  0
 5 2016  0  0 0  0  0  0  0
 6 2007  0  0 0  0  0  0  0
 6 2008  0  0 0  0  0  0  0
 6 2009  0  0 0  0  0  0  0
 6 2010  0  0 0  0  0  0  0
 6 2011  0  0 0  0  0  0  0
 6 2012  0  0 0  0  0  0  0
 6 2013  0  0 0  0  0  0  0
 6 2014  1  0 0  0  1  0  1
 6 2015  0  0 0  0  0  0  0
 6 2016  0  0 0  0  0  0  0
 7 2007  0  0 0  0  0  0  0
 7 2008  0  0 0  0  0  0  0
 7 2009  0  0 0  0  0  0  0
 7 2010  0  0 0  0  0  0  0
 7 2011  0  0 0  0  0  0  0
 7 2012  3  0 0  0  3  0  3
 7 2013  0  0 0  0  0  0  0
 7 2014  0  0 0  0  0  0  0
 7 2015  0  0 0  0  0  0  0
 7 2016  0  0 0  0  0  0  0
 8 2007  0  0 0  0  0  0  0
 8 2008  0  2 0  1  1  0  2
 8 2009  0  0 0  0  0  0  0
 8 2010  0  4 0  0  4  0  4
 8 2011  0  0 0  0  0  0  0
 8 2012  0  0 0  0  0  0  0
 8 2013  0  0 0  0  0  0  0
 8 2014  0  0 0  0  0  0  0
 8 2015  0  0 0  0  0  0  0
 8 2016  0  0 0  0  0  0  0
 9 2007  0  0 0  0  0  0  0
 9 2008  3  0 0  0  3  0  3
 9 2009  2  0 0  0  2  0  2
 9 2010  7  0 0  0  7  0  7
 9 2011 19 16 0  8 25  2 35
 9 2012 15 14 0  9 19  1 29
 9 2013 16 35 0  2 44  5 51
 9 2015 24 46 0 20 40 10 70
 9 2016  4  6 0  4  4  2 10
10 2007  0  0 0  0  0  0  0
10 2008  0  0 0  0  0  0  0
10 2009  0  0 0  0  0  0  0
10 2010  0  0 0  0  0  0  0
10 2011  0  2 0  0  2  0  2
10 2012  2  0 0  0  2  0  2
10 2013  6 19 0  0 22  3 25
10 2014  6  8 0  4 10  0 14
10 2015  3  0 0  0  3  0  3
10 2016  0  4 0  4  0  0  4
11 2007  0  0 0  0  0  0  0
11 2008  0  0 0  0  0  0  0
11 2009  2  0 0  0  2  0  2
11 2010  1  0 0  0  1  0  1
11 2011  3  2 0  3  2  0  5
11 2012  5  3 0  2  5  1  8
11 2013  0 11 0  0  6  5 11
11 2014  9 12 0 11  8  2 21
11 2015  1  2 0  0  3  0  3
11 2016  2  4 0  4  2  0  6
12 2007  0  0 0  0  0  0  0
end
format %ty year
------------------ copy up to and including the previous line ------------------


Thanks for the help.
Kind Regards,
Anita