Generating Variable Based on Multiple Conditions

[Bobby Dack]

Hi all,

I am currently working on a stata dataset in regards to institutional holdings. I would like to count how many institutions hold a particular stock in one quarter. Below is an example of my database.

country mgrno ticker yq prc shares change
AUSTRALIA 8741 ACC 2016q1 47.09 435739
AUSTRALIA 8741 ACC 2016q2 52.87 704029
AUSTRALIA 8741 ACC 2016q3 50.87 725429
AUSTRALIA 8741 ACC 2016q4 49.77 842215
AUSTRALIA 8741 ACC 2017q1 47.59 724819
AUSTRALIA 8741 ACC 2017q2 47.3 480631
AUSTRALIA 8741 ACC 2017q3 44.15 459211
AUSTRALIA 8741 ACC 2017q4 41.03 448423
AUSTRALIA 8741 ACC 2018q1 38.62 646078
AUSTRALIA 8741 ACC 2018q2 42.88 636689
AUSTRALIA 8741 ACC 2018q3 41.16 362859
AUSTRALIA 8741 ACC 2018q4 41.39 903309
AUSTRALIA 8741 AKR 2011q4 20.14 538837 290637
AUSTRALIA 8741 AKR 2012q1 22.54 419013 170813
AUSTRALIA 8741 AKR 2012q2 23.18 116057 -132143
AUSTRALIA 8741 AKR 2012q3 24.82 169857 -78343
AUSTRALIA 8741 AKR 2012q4 25.08 172857 -75343
AUSTRALIA 8741 AKR 2013q1 27.77 154357 -93843
AUSTRALIA 8741 AKR 2013q2 25.14 164652 -83548
AUSTRALIA 8741 AKR 2014q3 27.58 936377 688177
AUSTRALIA 8741 AKR 2014q4 32.03 1122871 874671
AUSTRALIA 8741 AKR 2015q1 34.88 775055 526855
AUSTRALIA 8741 AKR 2015q2 29.59 829421 581221
AUSTRALIA 8741 AKR 2015q4 33.15 749269 501069
AUSTRALIA 8741 AKR 2016q1 35.13 1200440 952240
AUSTRALIA 8741 AKR 2016q2 35.52 1264233 1016033
AUSTRALIA 8741 AKR 2016q3 36.24 1302733 1054533
AUSTRALIA 8741 AKR 2016q4 32.68 883462 635262

In this case, I would like to count how many institutions, by mgrno, hold stock, for example, "ACC" in 2016q1. I would like to be able to cover all stocks of all quarters in the dataset.


Thank you very much!

[Joro Kolev]

Use -dataex- to present your sample because the way how you show your data it cannot be imported (at least not easily).

Also be more specific what outcome you expect. You want to create a new variable for each ticker?

[Bobby Dack]

Use -dataex- to present your sample because the way how you show your data it cannot be imported (at least not easily).

Also be more specific what outcome you expect. You want to create a new variable for each ticker?

I would like to create a new variable, which shows how many institutions (mgrno) hold a particular ticker at a particular date (by year and quarter).

[Ken Chui]

Still no -dataex-. Please follow the FAQ (https://www.statalist.org/forums/help) and try this:

* Install it if you are running Stata 16 or before:
ssc intall dataex

* Extract the data:
dataex country mgrno ticker yq prc shares change if country=="AUSTRALIA"

Read the output careful and post the sample data code generated here. Without that it's hard to help.

[Bobby Dack]

Still no -dataex-. Please follow the FAQ (https://www.statalist.org/forums/help) and try this:

* Install it if you are running Stata 16 or before:
ssc intall dataex

* Extract the data:
dataex country mgrno ticker yq prc shares change if country=="AUSTRALIA"

Read the output careful and post the sample data code generated here. Without that it's hard to help.

Thank you for your explanation as I did not know what dataex is. I will come back to it and upload the dataex