Please help me out. I would like to eventually create a new variable which is a proportion, called Raw∆k;t = Number of institutions buying k;t / (Number of institutions buying k;t + Number of institutions selling k;t ) where k is a ticker that is bought or sold during quarter t.
The variables ticker, k and qdate, t are string variables. I have an ID variable that identifies each institution, order and q_status which are string variables. The observation in the variable q_status has either + or - or 0, as a position at the end of the quarter, meaning the status increased, decreased or remained the same. The number of institutions is essentially, the number of +, for each stock k during the quarter t. My problem is to identify a code that counts the number of +, i.e the q_status variable and then also counts the number of -, for each ticker during the quarter and place that value in a column within stata.
What i tried to do is run the line of code below which counts the +, by qdate (which is the quarter but does not count for each stock k). My challenge is to count based on stock k and qdate t and place the result in a column. I trust that if i count the + and then - , i should be able to generate a code to add them and obtain the ratio. The intenton is to generate the ratio.
my sample data is as below
The variables ticker, k and qdate, t are string variables. I have an ID variable that identifies each institution, order and q_status which are string variables. The observation in the variable q_status has either + or - or 0, as a position at the end of the quarter, meaning the status increased, decreased or remained the same. The number of institutions is essentially, the number of +, for each stock k during the quarter t. My problem is to identify a code that counts the number of +, i.e the q_status variable and then also counts the number of -, for each ticker during the quarter and place that value in a column within stata.
What i tried to do is run the line of code below which counts the +, by qdate (which is the quarter but does not count for each stock k). My challenge is to count based on stock k and qdate t and place the result in a column. I trust that if i count the + and then - , i should be able to generate a code to add them and obtain the ratio. The intenton is to generate the ratio.
Code:
by qdate:count if q_status == "+"
Code:
* Example generated by -dataex-. To install :ssc install dataex * dataex investor_id ticker order qdate q_status clear input int investor_id str3 ticker str1 order str6 qdate str1 q_status . "" "" "" "" . "" "" "" "" 40 "FBK" "S" "2007q1" "-" 108 "FBK" "B" "2007q1" "-" 95 "FBK" "B" "2007q1" "+" 72 "FBK" "B" "2007q1" "+" 120 "IL " "B" "2007q1" "+" 44 "IL " "B" "2007q1" "+" 45 "IL " "S" "2007q1" "-" 72 "IL " "S" "2007q1" "-" 108 "IL " "B" "2007q1" "-" 14 "IL " "B" "2007q1" "+" 118 "NC" "S" "2007q1" "-" 14 "NC" "B" "2007q1" "+" 108 "NC" "S" "2007q1" "-" 41 "NMG" "B" "2007q1" "+" 40 "NMG" "S" "2007q1" "-" 108 "NMG" "B" "2007q1" "-" 14 "NMG" "B" "2007q1" "+" 72 "NMG" "B" "2007q1" "0" 87 "NMG" "S" "2007q1" "-" end
Comment