conditions by group

[Ylenia Curci]

Hi there,
I have the following data and I want a variable that is 1 if, by firmid, there is at least one observation within the group for which x==1 & y==1, is 2 if, by firmid, all the observations within the group have x==1 & y==0 and, is 3 if, by firmid, all the observations within the group have x==0 & y==1.

Thank you

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 firmid float(x y)
"00705004000018" 0 1
"00708002100012" 1 0
"00708002100012" 0 1
"00708002100012" 0 1
"00708002100012" 0 1
"01545059600026" 0 1
"01585179300131" 0 1
"01585179300131" 0 1
"01625002900739" 1 0
"01625002900739" 1 0
"03652015300028" 0 1
"04705017400029" 0 1
"05480095800248" 0 1
"05480095800248" 0 1
"05480095800503" 0 1
"05480095800503" 0 1
"05480654200012" 0 1
"05480654200012" 0 1
"05480654200012" 0 1
"05480654200012" 0 1
"05480654200012" 0 1
"05480654203602" 0 1
"05480654203602" 0 1
"05480654203602" 0 1
"05480654203602" 0 1
"05480654203602" 0 1
"05580925500015" 0 1
"05650171100115" 0 1
"05650171100115" 1 0
"05680065901435" 1 0
"05680065901435" 0 1
"05680065901435" 1 0
"05720137800023" 0 1
"05720137800023" 0 1
"05720137800023" 0 1
"05750274200661" 0 1
"05750274200661" 0 1
"05780615000017" 0 1
"05820018900015" 1 0
"05820018900015" 0 1
"05980406200053" 0 1
"06020012800019" 1 1
"06080139600023" 0 1
"06150024500016" 0 1
"06150024500016" 0 1
"06150024500016" 0 1
"06150024500016" 0 1
"06150024500016" 1 0
"06150024500016" 0 1
"06150024500016" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 1 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 1 0
"06220100900222" 1 1
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900222" 1 0
"06220100900222" 1 1
"06220100900222" 1 1
"06220100900222" 1 1
"06220100900222" 1 1
"06220100900222" 1 0
"06220100900222" 0 1
"06220100900222" 0 1
"06220100900347" 1 0
"06220100900347" 0 1
"06220100900347" 0 1
"06220100900347" 0 1
"06220100900347" 1 1
"06220100900347" 1 0
"06220100900347" 0 1
"06220100900347" 1 0
"06220100900347" 1 1
"06220100900347" 0 1
end

[Carlo Lazzaro]

Ylenia:
you may want to try:

. egen wanted_1=group(x y) if x==1 & y==1

. egen wanted_2=group(x y) if x==1 & y==0

. egen wanted_3=group(x y) if x==0 & y==1

. replace wanted_2= wanted_2+1 if wanted_2 !=.

. replace wanted_3= wanted_3+2 if wanted_3 !=.

. egen overall_wanted=rowtotal( wanted_1 wanted_2 wanted_3)

.

You can use the -tag- function from -egen- to identify one observation only when -overall_wanted=1-

[Ylenia Curci]

Hi Carlo, thank you.
Sorry I was not clear enough, the group is given by the firmid, so the conditions have to be met among the observations with the same firmid.
Thanks

[Andrew Musau]

bys firmid: egen tot1= total(x==1 & y==1)
bys firmid: egen tot2= total(x==1 & y==0)
bys firmid: egen tot3= total(x==0 & y==1)
bys firmid: gen wanted= cond(tot1>=1, 1, cond(tot2==_N, 2, cond(tot3==_N, 3, .)))

[Carlo Lazzaro]

Ylenia:

. bysort firmid: gen wanted_1=1 if x==1 & y==1


. bysort firmid: gen wanted_2=2 if x==1 & y==0


. bysort firmid: gen wanted_3=3 if x==0 & y==1


. gen overall_wanted= wanted_1


. replace overall_wanted= wanted_2 if overall_wanted==.


. replace overall_wanted= wanted_3 if overall_wanted==.


. bysort firmid: egen check_1=min( overall_wanted)

. bysort firmid: egen check_2=max( overall_wanted)

. g research_goal=1 if check_1==check_2


. replace research_goal=0 if research_goal==.

[Ylenia Curci]

I came up with this code that, apparently, works.

bys  firmid: egen sumx=sum(x)
bys firmid: egen sumy=sum(y)

gen wanted=1
replace wanted=2 if x>0 & y==0
replace wanted=3 if x==0 & y>0

Andrew, your code is much more elengant but it creates a lot of missing when x==1 & y==1, although not always. Any idea why?

bys firmid: egen tot1= total(x==1 & y==1)
bys firmid: egen tot2= total(x==1 & y==0)
bys firmid: egen tot3= total(x==0 & y==1)
bys firmid: gen wanted= cond(tot1>=1, 1, cond(tot2==_N, 2, cond(tot3==_N, 3, .)))

[Andrew Musau]

I am guessing it has to do with the interpretation

is 1 if, by firmid, there is at least one observation within the group for which x==1 & y==1

My interpretation was that these conditions have to hold simultaneously (i.e. within the same observation). Please post a data example that shows a case where the code fails.

[Carlo Lazzaro]

I interpret Ylenia's post as Andrew did.

[Ylenia Curci]

Ah yes, ok. No, the condition can hold within the same group, so my original request was wrong. Anyway thank you Andrew, and thank you Carlo, I do not know what in your code made me come up with the solution I found.