Hello all, I am trying to calculate the percentage of each firm (Firm_ID)’s outstanding shares (share_outstanding) held by funds (‘fundno’ for fund id; and ‘shares’ is the number of firm’s shares held by a fund in a year) in each year (year). My problem is, although in most cases, each group (i.e., each ‘Firm_ID’ in each ‘year’) only has one ‘share_outstanding’ value, in some cases a group includes more than one ‘share_outstanding’ value. By following Table1 as an example, data table1;
infile cards dsd dlm=",";
input
firm_id :$15.
year :8.
fundno :$15.
shares :8.
share_outstanding :8.
;
cards;
36110,2002, 14086, 18000, 32000000
36110,2002, 15056, 92000, 32000000
36110,2002, 15058, 4740, 32000000
36110,2002, 16500, 35300, 27000000
36111,2002, 1903, 26700, 32000000
36111,2002, 1909, 543350, 32000000
36111,2002, 19658, 60000, 30000000
36111,2002, 2021, 20000, 30000000
36111,2002, 2034, 59700, 32000000
36110,2003, 14086, 18000, 32000000
36110,2003, 15056, 92000, 32000000
36110,2003, 15058, 4740, 37000000
36110,2003, 16500, 35300, 27000000
36111,2003, 1903, 26700, 32000000
36111,2003, 1909, 543350, 31000000
36111,2003, 19658, 60000, 33000000
36111,2003, 2021, 20000, 30000000
;;;;
run;
There are four groups (same Firm_iD, and same year) in Table 1. By using the first group as an example, There are three ‘32000000’ and one ‘27000000’ in this group (i.e., Firm_ID=36110 and year=2002), and then I assume the outstanding share of Firm_ID ‘36110’ in ‘year’ 2002 is ‘32000000’. I sum the number of shares held by funds in this group, which is 150040 (=18000+92000+4740+35300) I divide the number of shares held by funds in this group (150040) by the firm’s outstanding shares (32000000) to get a new variable ‘fund_ratio‘, which equals 0.00468875. In the end, I expect to get Firm_ID year fund_ratio 36110 2002 0.00468875 Following the same rule, in the second group (i.e., Firm_ID=36111 and year=2002), I have three ‘32000000’ and two ‘30000000’. So, the fund_ratio=0.0221796875, which is (26700+543350+60000+20000+59700)/ 32000000. I expect to get Firm_ID year fund_ratio 36111 2002 0.0221796875 In the third (Firm_ID=36110,year=2003), there are two ‘32000000’ and two ‘37000000’. In this case, I keep them both. I expect to get Firm_ID year fund_ratio 36110 2003 0.00468875 36110 2003 0.00405514 As 0.00468875= (18000+92000+4740+35300)/ 32000000 0.00405514= (18000+92000+4740+35300)/ 37000000 fourth group (Firm_ID=36111, year=2003), I expect to get Firm_ID year fund_ratio 36111 2003 0.020314063 36111 2003 0.020969355 36111 2003 0.019698485 36111 2003 0.021668333 As 0.020314063 = (26700+543350+60000+ 20000)/32000000 0.020969355= (26700+543350+60000+ 20000)/ 31000000 0.019698485= (26700+543350+60000+ 20000)/ 33000000 0.021668333= (26700+543350+60000+ 20000)/ 30000000 I hope it is clear. Many thanks in advance for any advice.
... View more