generating a variable that indicates different accounting methods over several observations by company and by year

[Noa Kweekel]

Hi Everybody! I am new to this forum so I hope i have provided all the information needed in my message.

My data is panel data. I have yearly data on different companies in 4 different countries. When I tried to ttset the data, i noticed that for several companies i have duplicate years but with different accounting methods.
so for example for Company A, i have 2 observations for the year 2014 but with either Unconsolidated or Consilidated data. there are also companies that only report with unconsolidated or consolidated data, so here there are no duplicates.

variablenames:
IDNRCODES = id of a company
CLOSDATE_year = year
ACCDATA = indicates if the data is either unconsildated (4) or consolidated (1), .

I tried to gen a variable "MULTIPLEACC" that could indicate whether or not a company has data over dfifferent observations with both unconsolidated or unconsolidated data, so I would be able to specifically drop observations. But i could not make it work. Probably since an observation itself can not have both 1 and 4 in the ACCDATA cell.

command i used:
bysort IDNRCODES(CLOSDATE_year) : gen MULTIPLEACC = (ACCDATA==1 & ACCDATA==4)

(I am using Stata/MP 17.0)
is there anyone who has an idea how i could handle this?

thank you!

[Clyde Schechter]

I tried to gen a variable "MULTIPLEACC" that could indicate whether or not a company has data over dfifferent observations with both unconsolidated or unconsolidated data, so I would be able to specifically drop observations.

bysort IDNRCODES (CLOSDATE_year) : egen byte accdata1 = max(ACCDATA == 1)
by IDNRCODES (CLOSDATE_year): egen byte accdata4 = max(ACCDATA == 4)
gen byte MULTIPLEACC = accdata1 & accdata4

This will set MULTIPLEACC = 1 in every observation of any IDNRCODES that has at least one observation with ACCDATA == 1 and at least one observation with ACCDATA == 4, and 0 for all other IDNRCODES.

It isn't clear to me, however, whether that is what you are actually looking for, because from your second paragraph, I got the impression that you were concerned about an IDNRCODES that has both an observation with ACCDATA == 1 and another with ACCDATA == 4 in the same year. If this is what you want, modify the code I have just shown by removing the parentheses around CLOSDATE_year in both places.

Hi Everybody! I am new to this forum so I hope i have provided all the information needed in my message.

Well, your description is pretty good, but it does not actually completely pin down the details of how your data is organized. I felt sufficiently confident about filling in what is missing from your description, in part by seeing the code you tried. But in general, it is better to show actual example Stata data so that those who want to help you can be certain that the code they write is appropriate for the data you actually have, not the data they assume or guess you have, and also so they can test their code in your data. The way to do that is by using the -dataex- command. If you are running version 17, 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.

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

[Noa Kweekel]

Thank you so much for your response! I really appreciate it.
I removed the parentheses and that code was exactly what I needed. I am now able to drop the specific observations.

And also thank you for the tips on including the data. I will do this next time.