use observation as a varlist

[jean cereghetti]

Hi everyone,
I am currently struggling with something that I think is easy but i really couldn't figure out. I tried to explore all the possibilities in data manager but nothing worked.
I am using a dataset that contains a variable called refrencespouse (each person if has a spouse have a number). I am interested in the values of this variable and i want to use all of them in a egen command.

Meaning
egen example=anymatch(referencespouse), values(22 33 44 55 ...all observations)

I have tried to copy and paste all the observation but when I paste all this value are vertical and does not run in Stata. I have also tried to copy in Excel, transpose and then paste in Stata but all the observation are put together as a unique number.

I hope that my explanation is not too confusing, I can provide a screenshot if it is useful.

Thank everyone that has an idea,

Cereghetti Jean

[Maarten Buis]

Are you trying to find out if the spouse is present in the data?

We definitely do not want a screenshot. What would make this question a lot easier to answer if you gave us an extract of the relevant variables from your data. I made a guess about your datastructure in the example below. That is extra work for me, that was not necessary. So I would have appreciated if you prevented that by giving me the extract. Moreover, my guess could be wrong. In that case we would both have wasted our time. To create an extract you use the dataex command.

// create some example data
clear
input id spouseid  x
      1  .         2
      2  3         3
      3  2         8
      4  6         7
      5  7         6
end

// save a copy of the data
tempfile data
save `data'

// keep only the spouseid
keep spouseid

// rename it to id
rename spouseid id

// merge the original data with the spouse data
merge 1:1 id using `data'

// merge == 1 is id is in spouse data, but not in main data
drop if _merge == 1

// turn that into an indicator variable
gen spousenotindata = _merge == 2 if !missing(spouseid)

// we no longer need _merge
drop _merge

// admire the result
list

[jean cereghetti]

Thank you so much for your answer,
no I aready know if the spouse is present or not but i want to copy all the reference number and i will use them as a list of for a egen command.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte idint long(idhous99 hdate99) byte hlingu99 long(refper99 rpspou99)
-3     391 14701 1    39102    39101
-3   2241 14563 1   224102   224101
-3   2601 14543 1   260102   260101
-3   3331 14705 1   333102   333101
-3   4291 14545 1   429102   429101
-3   5011 14516 1   501102   501101
-3   7201 14537 1   720102   720101
-3   7561 14571 2   756101   756102
-3  10611 14699 1  1061101  1061102
-3  11311 14543 1  1131102  1131101
-3  13451 14699 1  1345102  1345101
-3  13571 14704 1  1357101  1357102
-3  13781 14545 2  1378101  1378102
-3  15981 14545 2  1598102  1598101
-3  15991 14538 1  1599102  1599101
-3  18381 14558 2  1838102  1838101
-3  19001 14516 2  1900102  1900101
-3  21951 14658 2  2195102  2195101
-3  22441 14706 2  2244102  2244101
-3  25441 14545 2  2544101  2544102
-3  25581 14699 2  2558102  2558101
-3  27401 14565 2  2740101  2740102
-3  29781 14550 2  2978102  2978101
-3  31461 14706 2  3146102  3146101
-3  32511 14705 2  3251102  3251101
-3  33271 14584 2  3327101  3327102
-3  34941 14635 2  3494102  3494101
-3  36731 14699 2  3673102  3673101
-3  37831 14571 2  3783102  3783101
-3  38321 14546 2  3832102  3832101
-3  40301 14550 2  4030101  4030102
-3  41161 14571 3  4116102  4116101
-3  41681 14586 3  4168102  4168101
-3  42061 14627 3  4206102  4206101
-3  42271 14661 3  4227102  4227101
-3  43761 14574 3  4376102  4376101
-3  43811 14686 3  4381101  4381102
-3  43921 14686 3  4392101  4392102
-3  44511 14586 3  4451102  4451101
-3  44821 14690 1  4482102  4482101
-3  45721 14566 1  4572101  4572102
-3  47171 14570 1  4717101  4717102
-3  50621 14579 1  5062102  5062101
-3  52171 14662 1  5217101  5217102
-3  52401 14648 1  5240101  5240102
-3  54101 14636 1  5410102  5410101
-3  55241 14546 1  5524102  5524101
-3  55421 14641 1  5542102  5542101
-3  55561 14580 1  5556102  5556101
-3  56261 14582 1  5626102  5626101
-3  56911 14574 1  5691101  5691102
-3  57211 14585 1  5721101  5721102
-3  58151 14661 1  5815102  5815101
-3  60811 14705 2  6081101  6081102
-3  61711 14601 2  6171102  6171101
-3  64921 14686 2  6492102  6492101
-3  65481 14563 2  6548101  6548102
-3  67111 14648 2  6711102  6711101
-3  67171 14668 2  6717102  6717101
-3  68171 14572 2  6817101  6817102
-3  68911 14568 2  6891102  6891101
-3  69521 14558 2  6952102  6952101
-3  71271 14705 2  7127102  7127101
-3  71411 14570 2  7141102  7141101
-3  72221 14619 2  7222102  7222101
-3  72681 14584 2  7268102       -3
-3  73211 14661 2  7321102  7321101
-3  73261 14703 2  7326102  7326101
-3  74101 14582 2  7410101  7410102
-3  76681 14657 2  7668102  7668101
-3  76751 14702 3  7675102  7675101
-3  79391 14658 2  7939101  7939102
-3  80321 14702 2  8032101  8032102
-3  81251 14565 2  8125102  8125101
-3  81961 14560 2  8196102  8196101
-3  82801 14582 2  8280101  8280102
-3  83211 14702 2  8321102  8321101
-3  84271 14587 2  8427101  8427102
-3  87951 14702 2  8795104  8795103
-3  89301 14616 2  8930102  8930101
-3  89621 14702 2  8962102  8962101
-3  90931 14702 2  9093102  9093101
-3  91071 14615 2  9107101  9107102
-3  94461 14664 2  9446101  9446102
-3  95201 14614 2  9520102  9520101
-3  95731 14634 2  9573102  9573101
-3  97121 14598 2  9712102  9712101
-3  97361 14702 2  9736101  9736102
-3  97961 14702 2  9796101  9796102
-3  98101 14599 2  9810102  9810101
-3 100041 14686 2 10004102 10004101
-3 101431 14702 2 10143102 10143101
-3 102081 14702 2 10208102 10208101
-3 102241 14633 2 10224101 10224102
-3 105591 14594 2 10559101 10559102
-3 105791 14702 2 10579101 10579102
-3 106411 14592 2 10641102 10641101
-3 106881 14702 2 10688102 10688101
-3 108531 14641 2 10853102 10853101
-3 108971 14599 2 10897101 10897102
end
format %tdD_m_Y hdate99
label values idint IDINT
label def IDINT -3 "inapplicable", modify
label values idhous99 IDHOUS99
label values hdate99 HDATE99
label values hlingu99 HLINGU99
label def HLINGU99 1 "french", modify
label def HLINGU99 2 "german", modify
label def HLINGU99 3 "italian", modify
label values refper99 REFPER99
label values rpspou99 RPSPOU99
label def RPSPOU99 -3 "inapplicable", modify

I hope that with the code it will be easier to understand what I mean.
Thank you Maarten

[Maarten Buis]

This seems to be a case of an xy-problem ( https://en.wikipedia.org/wiki/XY_problem )

The list you are looking for is going to be too long, so that is not going to work. Instead there is a real problem that you think you can solve with that list that you are not telling us. So right now all we can tell you is "that is not going to work", but we cannot tell you how to solve your real problem, because you did not tell us. So the solution is to take a step back, and tell us what you really want to do.

[jean cereghetti]

I have 18 waves of a study that is collected each year in household in Switzerland, in all the wave households and individual have been interviewed. I want to create a unique file with all the 18 waves to test a hypothesis. I previously have done it with the idhousehold(a identifier for the household), but when i create a unique file every member of the family is taken in account. So I have found this variable rpspouse and referper to get rid of member of the family that i dont need. this is why I need this list, i want to remove child, grand-parents and other member and keep only the couple.

[Maarten Buis]

It is still a bit unclear: the concept of a "unique file" is not clear to me. I suspect it means something like you want to keep the reference person and his or her spouse and drop all other household members. If that is the case, then it still looks like a problem you can solve with the basic strategy I outlined in #2.

[William Lisowski]

It appears to me that you have two datasets.

• the dataset you showed us, containing observations of households and for each household gives the individual identifiers for the "reference person" and that person's spouse
• a second dataset that you have not showed us contaning observations of individuals

You want to use the identifiers of the reference persons and their spouses to identify which individuals in the second dataset to keep, and discard the rest.

Below is example code using just a few observations of your example data and inventing observations of individuals. Perhaps it will start you in a useful direction. The key to what you want to do is to construct a reference dataset and use merge to combine it with your master dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(idhous99 refper99 rpspou99)
72221 7222102 7222101
72681 7268102      -3
74101 7410101 7410102
end

rename (refper99 rpspou99) (id_ref99 id_spo99)
reshape long id_, i(idhous99) j(role) string
rename id_ idper99
drop if idper99==-3
tempfile ref
save `ref'
* reference dataset
list, noobs sepby(idhous99)

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(idhous99 idper99 age99)
72221 7222101 42
72221 7222102 43
72221 7222103 12
72221 7222104  7
72681 7268101 21
72681 7268102 20
74101 7410101 65
74101 7410102 64
74101 7410110 23
74101 7410111 22
74101 7410112  2
99999 9999901 99
end

merge 1:1 idhous99 idper99 using `ref'
list, noobs sepby(idhous99)
keep if _merge==3
drop _merge
list, noobs sepby(idhous99)

. * reference dataset
. list, noobs sepby(idhous99)

  +----------------------------+
  | idhous99    role   idper99 |
  |----------------------------|
  |    72221   ref99   7222102 |
  |    72221   spo99   7222101 |
  |----------------------------|
  |    72681   ref99   7268102 |
  |----------------------------|
  |    74101   ref99   7410101 |
  |    74101   spo99   7410102 |
  +----------------------------+

. list, noobs sepby(idhous99)

  +------------------------------------------------------+
  | idhous99   idper99   age99    role            _merge |
  |------------------------------------------------------|
  |    72221   7222101      42   spo99       matched (3) |
  |    72221   7222102      43   ref99       matched (3) |
  |    72221   7222103      12           master only (1) |
  |    72221   7222104       7           master only (1) |
  |------------------------------------------------------|
  |    72681   7268101      21           master only (1) |
  |    72681   7268102      20   ref99       matched (3) |
  |------------------------------------------------------|
  |    74101   7410101      65   ref99       matched (3) |
  |    74101   7410102      64   spo99       matched (3) |
  |    74101   7410110      23           master only (1) |
  |    74101   7410111      22           master only (1) |
  |    74101   7410112       2           master only (1) |
  |------------------------------------------------------|
  |    99999   9999901      99           master only (1) |
  +------------------------------------------------------+

. keep if _merge==3
(7 observations deleted)

. drop _merge

. list, noobs sepby(idhous99)

  +------------------------------------+
  | idhous99   idper99   age99    role |
  |------------------------------------|
  |    72221   7222101      42   spo99 |
  |    72221   7222102      43   ref99 |
  |------------------------------------|
  |    72681   7268102      20   ref99 |
  |------------------------------------|
  |    74101   7410101      65   ref99 |
  |    74101   7410102      64   spo99 |
  +------------------------------------+