using inlist with external data

[Sungchul Park]

Hello,

I am trying to identify how to use inlist with external data. Currently, I have two separate data sets. The first data set is as follows:

id	a1	a2	a3	a4	a5	a6	a7	a8	a9
1	X21	X453	X324	D345	F54	G45	G3	V343	V453
2	D324	V32	D234	VGB23	D23	G23	V23	G23	GRD23
3	D23	G32	E32
4	D23	E3
5	G23	D23	DD43
7	DF32R	A32
8	B23	DFS32	F32
9	G2	D3	G23	G32	D23
9	G223	T23

The other data set is as follows and stored in an excel form:

G32

G23

G123

G32

G3

G2

G212

G32

D23

ED23

I'd like to identify people who have the same values in the second data set (e.g., G23 or G32). However, the issue is that the number of row in the second data set is about 10,000. Thus, I'd like to know how to create an efficient code. I'd be very glad if you could provide some thoughts.

Thank you!

[Nick Cox]

inlist() I don't think will help. This seems to me a problem in reshape and merge. Here I fixed your data examples to change or drop duplicates.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str5(a1 a2) str4 a3 str5 a4 str3(a5 a6 a7) str4 a8 str5 a9
 1 "X21"   "X453"  "X324" "D345"  "F54" "G45" "G3"  "V343" "V453" 
 2 "D324"  "V32"   "D234" "VGB23" "D23" "G23" "V23" "G23"  "GRD23"
 3 "D23"   "G32"   "E32"  ""      ""    ""    ""    ""     ""     
 4 "D23"   "E3"    ""     ""      ""    ""    ""    ""     ""     
 5 "G23"   "D23"   "DD43" ""      ""    ""    ""    ""     ""     
 7 "DF32R" "A32"   ""     ""      ""    ""    ""    ""     ""     
 8 "B23"   "DFS32" "F32"  ""      ""    ""    ""    ""     ""     
 9 "G2"    "D3"    "G23"  "G32"   "D23" ""    ""    ""     ""     
10 "G223"  "T23"   ""     ""      ""    ""    ""    ""     ""     
end

reshape long a, i(id)
drop if missing(a)
save data_1
clear

 
* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 a
"G23" 
"G123"
"G32" 
"G3"  
"G2"  
"G212"
"G32" 
"D23" 
"ED23"
end
duplicates drop a, force
merge 1:m a using data_1

list

     +-----------------------------------+
     |     a   id   _j            _merge |
     |-----------------------------------|
  1. |   D23    5    2       matched (3) |
  2. |  ED23    .    .   master only (1) |
  3. |  G123    .    .   master only (1) |
  4. |    G2    9    1       matched (3) |
  5. |  G212    .    .   master only (1) |
     |-----------------------------------|
  6. |   G23    9    3       matched (3) |
  7. |    G3    1    7       matched (3) |
  8. |   G32    3    2       matched (3) |
  9. |   A32    7    2    using only (2) |
 10. |   B23    8    1    using only (2) |
     |-----------------------------------|
 11. |   D23    9    5       matched (3) |
 12. |   D23    3    1       matched (3) |
 13. |   D23    2    5       matched (3) |
 14. |   D23    4    1       matched (3) |
 15. |  D234    2    3    using only (2) |
     |-----------------------------------|
 16. |    D3    9    2    using only (2) |
 17. |  D324    2    1    using only (2) |
 18. |  D345    1    4    using only (2) |
 19. |  DD43    5    3    using only (2) |
 20. | DF32R    7    1    using only (2) |
     |-----------------------------------|
 21. | DFS32    8    2    using only (2) |
 22. |    E3    4    2    using only (2) |
 23. |   E32    3    3    using only (2) |
 24. |   F32    8    3    using only (2) |
 25. |   F54    1    5    using only (2) |
     |-----------------------------------|
 26. |  G223   10    1    using only (2) |
 27. |   G23    5    1       matched (3) |
 28. |   G23    2    6       matched (3) |
 29. |   G23    2    8       matched (3) |
 30. |   G32    9    4       matched (3) |
     |-----------------------------------|
 31. |   G45    1    6    using only (2) |
 32. | GRD23    2    9    using only (2) |
 33. |   T23   10    2    using only (2) |
 34. |   V23    2    7    using only (2) |
 35. |   V32    2    2    using only (2) |
     |-----------------------------------|
 36. |  V343    1    8    using only (2) |
 37. |  V453    1    9    using only (2) |
 38. | VGB23    2    4    using only (2) |
 39. |   X21    1    1    using only (2) |
 40. |  X324    1    3    using only (2) |
     |-----------------------------------|
 41. |  X453    1    2    using only (2) |
     +-----------------------------------+

.