Help regarding transforming of patents into variable

[Doing Deals]

I am having trouble transforming a large dataset into the variables I need to use in my Master thesis and would like to request help as Googling has not lead to the right solution yet.

My variable is the percentage of a pharmaceutical firm's total drugs that have a patent awarded between 9 and 13 years ago in the given year in my time frame (2003 - 2017).

The dataset consists of 119k patents. The datasample for specific drugs I need is smaller, but still has an N of 9.000. Now, for a given year, I have a column with the firm name, the drug name and its patent year awarded. I have calculated the difference between the given year and the patent year, coded it with 0 if it is not between 9 and 13 years in 2003 and a 1 if it is. This all works fine, but I have a problem with the next step.

The number of firms in the sample is very large, and as there are 14 years, I can not manually calculate the variable for every firm. So I am looking for excel or stata to do this for me. I believe this might be possible with a formula that calculates the percentage per firm, but I do not know how to have excel or stata include only the percentage per every individual firm. I believe I could create a formula that includes only the number IF the value in the other column is for instance Merck. But for this sample I can not manually create this formula for every name in the dataset. So I hope that there is a function to calculate the percentage of the combined drugs, for every individual firm.

Would you have any suggestions for tackling this issue?


I hope my description of the issue was clear enough to comprehend.

[Clyde Schechter]

The solution to your problem depends on the layout of your data. Please use the -dataex- command to post an example of your data. Be sure the example you show includes observations from more than just one firm, and that within each firm you show there are some observations where the age of the patent is within your window and other observations where it is not.

If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Doing Deals]

Sorry, tried googling it, but couldn't figure out how to use that code correctly, so I posted an overview of my excel screenshot with the parameters you described.

Now for Eli Lilly and co, I need to obtain the percentage of patents in a given year between 2003 and 2017 that are between 9 and 13 years old.

[Nick Cox]

Googling it was never the answer. Clyde explained in detail in #2 that dataex is likely to be part of your Stata (14.2 or 15.1 up) and if not how to install it first using the Stata command ssc install dataex. You're also asked to tell us if you're not using Stata 15.1. Either way help dataex gives help.

By the way, posting under the identifier "Doing Deals" runs counter to the explicit request that people use real names (typically given and family names).

For more detail, please read and act on

https://www.statalist.org/forums/help#stata

https://www.statalist.org/forums/help#realnames

https://www.statalist.org/forums/help#version