Compare value in a cell with previous values in a panel data

[Rajat Khanna]

Hi guys - Need some help with comparing a value in a cell with previous values in a panel data. The is an inventor-year panel with a variable subclass that describes the subclasses in which each inventor was granted patent each year. The data looks something like this:

inventor year patent subclass
10069 1982 4346109 859
10069 1983 4382151 875
10069 1983 4386227 219
10069 1984 4481217 894
10069 1984 4468458 134
10069 1984 4435423 927
10069 1984 4454126 41
10069 1984 4454330 497
10069 1984 4454330 219
10069 1984 4455316 419
10069 1984 4482734 960
10069 1985 4522759 821
10069 1985 4491592 560
10069 1986 4571408 134
10069 1986 4599328 134
10069 1986 4603126 739
10069 1986 4613593 106
10069 1986 4576963 532
10069 1987 4655973 35
10069 1987 4639450 176

I want to compare values in the subclass and identify if the inventor has used the subclass in the previous 4 years and if yes, generate a new variable that will return 1 and 0 otherwise. For example, the new variable, i,e., old subclass should return value of 1 if the inventor has used the subclasses in the last 4 years and 0 otherwise. To take an example from the table above, the old subclass variable should generate 1 for the green entry as the inventor has used the subclass 134 within last 4 years (1984) and 0 for red entry has the inventor has not used subclass 106 within previous 4 years. Hope you will be able to help.

Best
Rajat

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(inventor year) long patent int subclass
10069 1982 4346109 859
10069 1983 4382151 875
10069 1983 4386227 219
10069 1984 4481217 894
10069 1984 4468458 134
10069 1984 4435423 927
10069 1984 4454126  41
10069 1984 4454330 497
10069 1984 4454330 219
10069 1984 4455316 419
10069 1984 4482734 960
10069 1985 4522759 821
10069 1985 4491592 560
10069 1986 4571408 134
10069 1986 4599328 134
10069 1986 4603126 739
10069 1986 4613593 106
10069 1986 4576963 532
10069 1987 4655973  35
10069 1987 4639450 176
end

rangestat (count) old_subclass = patent, by(inventor subclass) interval(year -4 -1)
replace old_subclass = 0 if missing(old_subclass)
replace old_subclass = !!old_subclass

Note: -rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have in this response. If you are running version 16 or a fully updated version 15.1 or 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.

[Rajat Khanna]

Thanks a lot, Clyde. The code works perfectly. I will keep in mind about using -dataex- the next time I need to post data example. Thanks again.

Rajat

[Yelitza Ponton]

Hi Clyde Schechter, I would like to do something similar but with a categorical variable. I tried to use the same code, however, since it is a categorical variable I get the following error: "Variable_name found where numeric variable expected".

What should I change in the code to make it work?

Thanks in advance!

Best,

Yelitza

[Clyde Schechter]

I have no idea what you are saying. Similar how? What categorical variable? All of the variables in the original post of #1 are categorical. If you want advice, please use -dataex- to show example data and also show the exact command(s) you used, along with the exact error message you got and any other output that accompanied it. See #2 in this thread for information about using -dataex- if you are not familiar with it.