combining foreach and by with strings

[Giovanna Capponi]

Hello,

I found similar topics on the forum but the solutions provided do not seem to fit my case.

I have 3 string variables:

- an invention identifier "case"
- a variable with the inventors collaborating on an invention "invid"
- a variable with the main collaborator of each inventor "main_collab"

There is one fixed main collaborator for each inventor (the relationship can be mutual or not), I would like to create a variable "common" identifying inventions on which each inventor-collaborator couple co-invent.

Data are long and look as follows:

case	invid	main_collab	common
1	A	B	1
1	B	C	1
1	C	D	.
2	D	H	.
2	G	F	.

"common" would then signal that inventors A and B collaborate with their main collaborator (B and C, respectively) on case "1".

The closest I got to obtain what I want is:

levelsof main_collab, local(levels)
gen common=.
foreach l of local levels {
replace common=1 if invid=="`l'"
}

However:
- common switches to 1 when the collaborator is in the invid column
- I would like to run the loop at "case" level but I don't know how to combine foreach and by.

Hopefully I managed to explain myself, thanks in advance for any suggestion!

Giovanna

[Wouter Wakker]

Hi, I think the -countmatch- from SSC can help you. It checks whether the value of one variable is also present in another variable.

clear
input byte case str1(invid main_collab)
1 "A" "B"    
1 "B" "C"
1 "C" "D"    
2 "D" "H"    
2 "G" "F"    
end

ssc install countmatch
countmatch main_collab invid, by(case) gen(common)
list

. list

     +----------------------------------+
     | case   invid   main_c~b   common |
     |----------------------------------|
  1. |    1       A          B        1 |
  2. |    1       B          C        1 |
  3. |    1       C          D        0 |
  4. |    2       D          H        0 |
  5. |    2       G          F        0 |
     +----------------------------------+

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte case str1(invid main_collab) byte common
1 "A" "B" 1
1 "B" "C" 1
1 "C" "D" .
2 "D" "H" .
2 "G" "F" .
end


gen caseid= string(case)+invid
gen casem=string(case)+ main_collab
expand 2, gen(set)
replace casem= caseid if set
bys casem: egen wanted= max(casem==casem[_n+1])
drop if set

Result:

. list case invid main_collab common wanted, sep(10)

     +-------------------------------------------+
     | case   invid   main_c~b   common   wanted |
     |-------------------------------------------|
  1. |    1       A          B        1        1 |
  2. |    1       B          C        1        1 |
  3. |    1       C          D        .        0 |
  4. |    2       G          F        .        0 |
  5. |    2       D          H        .        0 |
     +-------------------------------------------+

[Giovanna Capponi]

Thank you so much! Now it works!