Flagging just one corresponding positive value for every negative value

[Karishma DSouza]

Hi. My dataset has an id variable, a total amount variable and the corresponding date variable. There are multiple observations per id and within a single id there are sometimes negative numbers of the same positive number in another observation. For every negative value I want to flag just one positive value. I don't have a clear identifier for a corresponding positive value.
For example, in the given table below, I want to flag the first two entries for id 1. For id 3, I want to flag the second and third (or fourth - but just one of these) entries. For id 4, I want to flag the first, second, third and fourth entry.

Id	Total_Amount	Date
1	34	03jul2018
1	-34	03jul2018
1	25	05may2015
2	13	12nov2016
2	14	11jun2019
3	43	01jan2014
3	-456	22mar2017
3	456	22mar2017
3	456	22mar2017
4	756	28apr2015
4	756	28apr2015
4	-756	28apr2015
4	-756	28apr2015
4	143	03oct2017

[Joro Kolev]

In this thread here, Clyde Schechter showed you how to flag pairs, or larger groups that have positive and corresponding negative value.
https://www.statalist.org/forums/for...s-within-an-id

Once you have this flag Clyde showed you how to construct in the thread above, lets call it flagcorresponding, then to select only positive from such pairs or larger groups you do

gen flagpositive = flagcorresponding & Total_Amount>0

[Nick Cox]

In the thread cited Clyde gave this excellent advice, which still applies.

If you have brought your data into Stata, then the helpful thing to do is to give an example from the Stata data set, and use the -dataex- command for that purpose. Not only will it save a great deal of time for whoever chooses to try to help you, it will also be quicker and easier for you! 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.

I didn't understand the reason you gave for not using dataex. You have scope to enter data like those in #1 into Stata and then copy and paste the results into Statalist, just as you copied and pasted your tableau.

[Karishma DSouza]

Joro Kolev Thank you, Joro. I think I was unclear in my question. The dataset has multiple instances per id where there is just one negative value but multiple corresponding positive values with the same dates. I want to create "absolute pairs", in that I want to flag the negative value and only one of the positive values. These positive values are undistinguishable from each other, and so for every negative value I want one positive value (of the same amount) chosen at random.

Nick Cox I did try that and for some reason it appears that I can't copy anything outside of the remote server. This has created multiple problems even otherwise for me since all information/documents can only be saved on the server. In the tableau above, I manually created sample data to illustrate the problem and did not copy and paste it from the server Stata application. Separately, so that I do this correctly, in instances such as this where I am returning to an old question after a break, should I be updating the old thread with the new question (i.e. should I have posted this on my thread with Clyde) or did I do this right by creating a new query/thread here?

[Nick Cox]

I don't really follow, but I can't solve the first problem. However, good practice here would be to fake a dataset as if it were code produced by dataex.

In general, the same question belongs in the same thread. We already have advice on that at #1 of https://www.statalist.org/forums/help#adviceextras

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int total_amount str9 date
1   34 "03jul2018"
1  -34 "03jul2018"
1   25 "05may2015"
2   13 "12nov2016"
2   14 "11jun2019"
3   43 "01jan2014"
3 -456 "22mar2017"
3  456 "22mar2017"
3  456 "22mar2017"
4  756 "28apr2015"
4  756 "28apr2015"
4 -756 "28apr2015"
4 -756 "28apr2015"
4  143 "03oct2017"
end


gen abs_val = abs(total_amount)

by id abs_val total_amount, sort: gen int counter = _N
by id abs_val(total_amount): egen how_many_to_delete = min(counter)
by id abs_val (total_amount): replace how_many_to_delete = 0 ///
    if total_amount[1] == total_amount[_N]
by id abs_val total_amount: gen byte drop_me = _n <= how_many_to_delete
drop if drop_me

Nick Cox I think I understand what Karishma's reason for not using -dataex- is. I once worked with a consulting client who provided me with access to a remote server that ran Stata and contained the data needed for my project. The server interface was configured in such away that it was not possible to copy and paste anything from it to my own computer (nor vice versa), not even what the server sent to my screen. It also severely restricted my internet access through the server to a handful of "approved" sites. So even if I ran -dataex- (this was before -dataex- was created) I would not have been able to transfer the output shown on my screen into the Forum accessed through my own computer, and I imagine that I would not have ben able to connect to Statalist through the server either. I was quite happy when that relationship ended! Perhaps Karishma is in a similar circumstance.

[Nick Cox]

Clyde Schechter So it’s a familiar problem. Thanks!

[Karishma DSouza]

Clyde Schechter Thank you for the code and for explaining the situation much better than I did. That is exactly the circumstance I am in and have two more years of that relationship to live out before I can relate to your feeling of happiness on it ending