Error with merging two datasets

[Hassan zahey]

Hello all,
I have individual datasets that have four waves ( i_indresp j_indresp k_indresp l_indresp), and each wave represents almost a year. I append these four waves to get this data "Main survey" :
"Main survey" :

* Example generated by -dataex-. For more info, type help dataex
clear
input double pidp long hidp int dvage byte jbstat float(wfh_always wfh_never) str1 wave int jbsoc10_cc
68004087 68006816 67 2 . . "i"  -9
68006127 68013616 47 6 . . "i"  -8
68020564 68013616 46 8 . . "i"  -8
68006807 68034016 80 4 . . "i"  -8
68008847 68040816 59 2 . . "i"  -8
68009527 68047616 39 2 . . "i" 312
68061288 68047616 31 2 . . "i" 413
68010887 68054416 53 2 . . "i" 614
68068082 68054416 56 1 . . "i"  -9
68011567 68061216 43 2 . . "i" 125
68020407 68095216 80 4 . . "i"  -8
68028567 68108816 46 3 . . "i" 711
68028571 68108816 50 2 . . "i" 614
68028575 68129216 26 6 . . "i"  -8
68157166 68129216 32 2 . . "i" 353
68029927 68136016 45 6 . . "i"  -8
68029931 68136016 48 2 . . "i" 356
68031967 68142816 69 4 . . "i"  -8
68035367 68156416 36 2 . . "i" 242
68142890 68156416 37 5 . . "i" 356
end
label values dvage i_dvage
label values jbstat i_jbstat
label def i_jbstat 1 "Self employed", modify
label def i_jbstat 2 "Paid employment(ft/pt)", modify
label values jbsoc10_cc i_jbsoc10_cc
label def i_jbsoc10_cc -9 "missing", modify
label def i_jbsoc10_cc -8 "inapplicable", modify
label def i_jbsoc10_cc 118 "Health and Social Services Managers and Directors", modify
label def i_jbsoc10_cc 125 "Managers and Proprietors in Other Services", modify
label def i_jbsoc10_cc 213 "Information Technology and Telecommunications Professionals", modify
label def i_jbsoc10_cc 223 "Nursing and Midwifery Professionals", modify
label def i_jbsoc10_cc 231 "Teaching and Educational Professionals", modify

I would like to merge the above data, "Main survey" with the data "Covid_waves."
"Covid_waves" :

* Example generated by -dataex-. For more info, type help dataex
clear
input long pidp float(furloughed wfh_always wfh_never wfh_can)
   76165 0 1 0 0
  280165 1 0 1 0
  469205 0 0 1 0
  599765 0 1 0 0
  732365 . 0 0 0
 1587125 . 0 0 1
 3424485 . 0 0 0
 4849085 0 0 1 0
68008847 0 0 1 0
68010887 0 0 1 0
68031967 . 0 0 0
68035365 . 0 0 0
68035367 0 1 0 0
68041487 0 1 0 0
68041491 1 0 1 0
68042171 0 1 0 0
68044207 0 1 0 0
68045567 0 1 0 0
68046247 . 0 0 0
68046251 . 0 0 0
68051007 . 1 0 0
68051011 0 0 1 0
68056463 . 0 0 0
68058487 . 0 0 0
68058491 . 0 0 0
68060527 0 1 0 0
68060531 0 1 0 0
68060533 . 0 0 0
68060537 . 0 0 0
68061288 0 0 1 0
68063247 0 0 1 0
68063927 0 0 1 0
68063931 0 0 1 0
68064605 . 0 0 0
68064609 . 0 0 0
68068007 0 0 1 0
68068011 0 0 1 0
68068015 0 1 0 0
68068082 . 0 1 0
68071407 0 0 1 0
68076171 . 0 0 0
68097245 . 0 0 0
68097927 . 0 0 0
68106767 . 0 0 0
68111531 . 0 0 0
68112211 1 0 1 0
68120367 . 0 0 0
68120375 0 1 0 0
68121047 . 0 0 0
68125127 0 0 1 0
68125131 0 1 0 0
68125135 0 0 0 1
68133285 . 0 0 0
68133289 . 0 0 0
68136009 0 1 0 0
68137365 . 0 0 0
68138045 . 0 0 0
68138049 . 0 0 0
68138051 . 0 0 0
68141447 . 0 0 0
68142139 1 0 1 0
68142890 0 1 0 0
68144847 0 1 0 0
end

All these data are produced from the same institution. These are individual-level data, with each row uniquely identified by "PIDP". Therefore, The "Covid_waves" can be linked to the main survey individual data using "pidp"

However, I tried to merge the "Covid_data" to the " Main survey" by this code.

merge m:1 pidp using " Main survey"

But I got this error: " variable pidp does not uniquely identify observations in the using data". I think this is not supposed to happen because the PIDP variable is unique for each individual. I'm not sure what is causing this problem is that my appending of the main survey waves was wrong or something else.

[Andrew Musau]

In your example datasets, the variable "pidp" is a unique identifier. In your full datasets, check if this is the case.

isid pidp

If you get an error message, examine the output from the following:

bys pidp: gen tag=_N>1
list if tag, sepby(pidp)

[Hassan zahey]

In your example datasets, the variable "pidp" is a unique identifier. In your full datasets, check if this is the case.

isid pidp

If you get an error message, examine the output from the following:

bys pidp: gen tag=_N>1
list if tag, sepby(pidp)

Thanks Andrew for your reply

I examined the data and I still have this error. Note that I have above 100000 obs in each data

[Andrew Musau]

The main survey has duplicates of "pidp", i.e., the same pidp at times is combined with different "hidp"s. You do not show output from the using dataset. To use merge, you need the identifier to be unique in at least one of the datasets. If this does not hold and there are no data errors, then you likely need joinby. I cannot be helpful unless you include samples of your datasets that are consistent with the real datasets. You clearly know how to use dataex from #1, so check whether you have duplicates in the using dataset and whether such observations are intended. If so, then provide a sample of the datasets that include duplicates in each and someone can offer code suggestions on how to combine these.

[Hassan zahey]

Thanks for your clifraction

I did a random tag for observations for both data, which now shows the "pidp" does not uniquely identify observations in both data.this sample of the datasets include duplicates


"The main survey":

* Example generated by -dataex-. For more info, type help dataex
clear
input double pidp long hidp int dvage byte(jbstat jbsat) double jbhrs str1 wave float wfh_always
   22445 276841620 34  5  2   45 "k" .
   22445 277344816 33  2  4   35 "i" .
   22445 277059218 33  2  6   30 "j" 1
   22445 276637622 35  6 -8   -8 "l" 0
   29925 619024416 40  2  6   24 "i" .
   29925 618222022 43  2  5   28 "l" 0
   29925 618385220 42  2  6   28 "k" .
   29925 618630018 41  2  7   23 "j" 0
   76165 141045620 36  5  5   35 "k" .
   76165 141460418 35  2  5   35 "j" 0
   76165 141657616 34  2  6    7 "i" .
   76165 140712422 37  2  5   35 "l" 0
  280165 754371618 39  2  5   40 "j" 1
  280165 753800422 41  1 -8   -8 "l" 0
  280165 754113220 40  2  5   38 "k" .
  280165 754793216 38  2  5   40 "i" .
  333205 414800018 28  2  6   40 "j" 0
  333205 414473620 29  2  6   37 "k" .
  333205 415106696 26  2  6   37 "i" .
  469205 415059096 27  5  5   42 "i" .
  469205 414738818 28  2  7   16 "j" 0
  469205 414412420 29  5  6   16 "k" .
  469205 414188022 30  2  6   16 "l" 0
  599765 210528700 32  2  6 36.5 "k" .
  599765 210881618 31  2  6 36.5 "j" 0
  599765 210167622 33  2  6 36.5 "l" 0
  599765 211282816 30  2  6 37.5 "i" .
  665045 210188022 38  2  4   20 "l" 0
  665045 210548420 37  2  3   20 "k" .
  665045 210902018 36  2  4   15 "j" 0
  732365 618698020 34  8 -8   -8 "k" .
  732365 619371216 32  8 -8   -8 "i" .
  732365 618949618 33  8 -8   -8 "j" 0
  732365 618507622 35  8 -8   -8 "l" 0
  760925 210188020 38  3 -7   -8 "k" .
  760925 210514418 37  8 -8   -8 "j" 0
 1587125 618269616 51  2  5   -8 "i" .
 1587125 617487622 54  1  5   -8 "l" 0
 1587125 617895618 52  1  3   -8 "j" 0
 1587125 617671220 53  1  3   -8 "k" .
 1697285 210364818 45  2  5 37.5 "j" 0
 1697285 210766016 44  2  5 37.5 "i" .
 2626845 822997216 39  2  7   20 "i" .
 2626845 822589218 40  2 -7   35 "j" 0
 2888645  76479616 28  2  6   38 "i" .
 2888645  76126018 29  2  6   38 "j" 0
 2888645  75085622 31  2 -8   -8 "l" 0
 3229325 551051618 49  3 -8   -8 "j" 0
 3229325 550711620 50  6 -8   -8 "k" .
 3424485  73521620 83  4 -8   -8 "k" .
 3424485  73195222 84  4 -8   -8 "l" 0
 3424485  73902418 82  4 -7   -8 "j" 0
 3667245 279602420 31  2  6   40 "k" .
 3667245 279942418 30  2  7   40 "j" 0
 3667245 280309616 29  2  4   40 "i" .
 3705325 144636018 64  4 -8   -8 "j" 0
 3705325 143697622 66  4 -8   -8 "l" 0
 3705325 144126020 65 97 -8   -8 "k" .
 3705325 144867216 63  4 -8   -8 "i" .
 4454005 481759622 75  4 -8   -8 "l" 0
 4454005 482840816 72  4 -8   -8 "i" .
 4454005 482106420 74  4 -8   -8 "k" .
 4454005 482446418 73  8 -8   -8 "j" 0
 4849085 346602822 37  2  3 37.5 "l" 1
 4849085 346990420 36  2  2 37.5 "k" .
 4849085 347554816 34  2  2 37.5 "i" .
 4849085 347255618 35  2  1 37.5 "j" 1
 4853165 824289216 47  2  5   37 "i" .
 4853165 823187622 50  2  7   40 "l" 0
 4853165 823500420 49  2  5   37 "k" .
 4853165 823820018 48  2  6   37 "j" 0
68002725  73025216 63  4 -8   -8 "i" .
68002725  72120822 66  4 -8   -8 "l" 0
68002725  72420020 65  4 -8   -8 "k" .
68002725  72739618 64  4 -8   -8 "j" 0
68004087  68006818 68  4 -8   -8 "j" 0
68004087  68006816 67  2  3   35 "i" .
68006127  68013618 48  3 -8   -8 "j" 0
68006127  68013620 49  3 -8   -8 "k" .
68006127  68013616 47  6 -8   -8 "i" .
68008847  68027220 61  2  7   39 "k" .
68008847  68040818 60  2  7   39 "j" 0
68008847  68040816 59  2 -8   -8 "i" .
68008847  68013622 62  2  7   39 "l" 0
68009527  68034020 41  2  5   39 "k" .
68009527  68047618 40  2  4 36.5 "j" 0
68009527  68020422 42  2  5 36.5 "l" 0
68009527  68047616 39  2  5   37 "i" .
68010887  68040820 55  2  6   32 "k" .
68010887  68027222 56  2  6   32 "l" 0
68010887  68054418 54  2  6   32 "j" 0
68010887  68054416 53  2  6   32 "i" .
68011567  68061216 43  2  6   50 "i" .
68011567  68061218 44  2  6 37.5 "j" 1
68014287  68054420 50 97 -8   -8 "k" .
68014287  68068018 49 97 -8   -8 "j" 0
68020407  68095218 81  4 -8   -8 "j" 0
68020407  68095216 80  4 -8   -8 "i" .
68020407  68081620 82  4 -8   -8 "k" .
68020564  68013618 47  3 -8   -8 "j" 0
end
label values dvage i_dvage
label values jbstat i_jbstat
label def i_jbstat 1 "Self employed", modify
label def i_jbstat 2 "Paid employment(ft/pt)", modify
label def i_jbstat 3 "Unemployed", modify
label def i_jbstat 4 "Retired", modify
label def i_jbstat 5 "On maternity leave", modify
label def i_jbstat 6 "Family care or home", modify
label def i_jbstat 8 "LT sick or disabled", modify
label def i_jbstat 97 "Doing something else", modify
label values jbsat i_jbsat
label def i_jbsat -8 "inapplicable", modify
label def i_jbsat -7 "proxy", modify
label def i_jbsat 1 "completely dissatisfied", modify
label def i_jbsat 2 "mostly dissatisfied", modify
label def i_jbsat 3 "somewhat dissatisfied", modify
label def i_jbsat 4 "neither satisfied or dissatisfied", modify
label def i_jbsat 5 "somewhat satisfied", modify
label def i_jbsat 6 "mostly satisfied", modify
label def i_jbsat 7 "completely satisfied", modify
label values jbhrs i_jbhrs
label def i_jbhrs -8 "inapplicable", modify

"Covid_waves"

* Example generated by -dataex-. For more info, type help dataex
clear
input long pidp float(furloughed wfh_always wfh_never wfh_can tag)
   76165 0 1 0 0 1
   76165 0 1 0 0 1
   76165 0 1 0 0 1
   76165 0 1 0 0 1
   76165 0 1 0 0 1
   76165 0 1 0 0 1
   76165 0 0 0 1 1
   76165 0 1 0 0 1
   76165 0 1 0 0 1
  280165 1 0 1 0 1
  280165 1 0 1 0 1
  280165 1 0 1 0 1
  280165 1 0 1 0 1
  280165 1 0 1 0 1
  469205 0 0 1 0 1
  469205 0 0 1 0 1
  469205 0 0 1 0 1
  599765 0 0 0 0 1
  599765 0 1 0 0 1
  599765 0 1 0 0 1
  599765 0 1 0 0 1
  732365 0 0 0 0 1
  732365 0 0 0 0 1
  732365 0 0 0 0 1
  732365 0 0 0 0 1
  732365 . 0 0 0 1
  732365 0 0 0 0 1
  732365 0 0 0 0 1
  732365 0 0 0 0 1
 1587125 . 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 1587125 0 0 0 1 1
 3424485 . 0 0 0 1
 3424485 0 0 0 0 1
 3424485 0 0 0 0 1
 3424485 0 0 0 0 1
 4849085 0 0 0 1 1
 4849085 0 1 0 0 1
 4849085 0 0 0 1 1
 4849085 0 0 0 1 1
 4849085 0 0 0 1 1
 4849085 0 1 0 0 1
 4849085 0 0 1 0 1
 4849085 0 0 0 1 1
 4849085 0 0 0 1 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68002725 0 0 0 0 1
68008847 0 0 1 0 1
68008847 0 0 1 0 1
68008847 0 0 1 0 1
68008847 0 0 1 0 1
68008847 0 0 0 0 1
68008847 0 0 1 0 1
68008847 0 0 1 0 1
68008847 0 0 1 0 1
68010887 0 0 1 0 1
68010887 0 0 0 1 1
68010887 0 0 1 0 1
68010887 0 0 0 1 1
68010887 0 0 1 0 1
68010887 0 0 0 1 1
68010887 0 0 1 0 1
68010887 0 0 0 1 1
68029931 0 1 0 0 1
68029931 0 1 0 0 1
68031967 0 0 0 0 1
68031967 . 0 0 0 1
68031967 0 0 0 0 1
68031967 0 0 0 0 1
68031967 0 0 0 0 1
68031967 0 0 0 0 1
68031967 0 0 0 0 1
68031967 0 0 0 0 1
68035365 0 0 0 0 1
68035365 . 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035365 0 0 0 0 1
68035367 0 1 0 0 1
68035367 1 0 0 1 1
68035367 0 0 0 1 1
68035367 0 0 0 1 1
68035367 1 0 0 1 1
68035367 1 0 0 1 1
end

What is the possible way to overcome this problem? How do use the joinby in this case? Do you have suggestions on how to combine these?

[Andrew Musau]

I am confused now. You have the variables "wfh_always" and "wfh_never" in both datasets. In addition, there are a lot of duplicate observations in the second (using) dataset. How do you want these 2 variables in addition to the identifier "pidp" to be combined as they are in both datasets? Secondly, what does an observation in the using dataset represent? How are the duplicate entries useful?

[Hassan zahey]

I am confused now. You have the variables "wfh_always" and "wfh_never" in both datasets. In addition, there are a lot of duplicate observations in the second (using) dataset. How do you want these 2 variables in addition to the identifier "pidp" to be combined as they are in both datasets? Secondly, what does an observation in the using dataset represent? How are the duplicate entries useful?

I generated the variables "wfh_always" and "wfh_never" in the "Main survey" to extract these variables from one categorical variable ( jbfxuse7 and jbflex7), so if these variables are confusing before merging, I can drop them until we merge the two datasets, then I can generate them again.

Let me explain what I did before reaching these two final datasets :

I have yearly individual wave datasets. I append four waves ( i, j, k, l ) for the period 2018 to 2021 together by this command, which we got the " Main survey":

local waves " i j k l"

foreach waves in  i { 
    use "`datadir'/i_indresp", clear
    rename i_* * 
    gen str1 wave = "i"
     keep pidp wave hidp ppid dvage jbstat qfhigh_dv racel_dv finnow finfut scghq* sclfsat1 sclfsat2 sclfsat7 sclfsato  livesp_dv cohab_dv single_dv hhtype_dv nchild_dv ndepchl_dv   scghq1_dv scghq2_dv sf12pcs_dv sf12mcs_dv  intdatd_dv intdatm_dv intdaty_dv finnow finfut fimnlabgrs_dv jbhrs jbstat jbsoc10_cc jbsic07_cc *stendreas* ccwork jbsat  country
     save datai, replace
}
foreach waves in local j  { 
    use "`datadir'/j_indresp", clear
rename j_* * 
gen str1 wave = "j"
  keep pidp wave hidp ppid dvage jbstat  qfhigh_dv racel_dv finnow finfut scghq* sclfsat1 sclfsat2 sclfsat7 sclfsato  livesp_dv cohab_dv single_dv hhtype_dv nchild_dv ndepchl_dv   scghq1_dv scghq2_dv sf12pcs_dv sf12mcs_dv  intdatd_dv intdatm_dv intdaty_dv finnow finfut fimnlabgrs_dv jbhrs jbstat jbsoc10_cc jbsic07_cc *stendreas* ccwork jbsat  country jbflex7 jbfxuse7
  
  gen wfh_always = (jbfxuse7 ==1 | jbflex7 ==1 )
  gen wfh_never = (jbfxuse7 ==0 | jbflex7 ==0)
save dataj, replace
}
foreach waves in local k  { 
    use "`datadir'/k_indresp", clear
rename k_* * 
gen str1 wave = "k"
  keep pidp wave hidp ppid dvage jbstat  qfhigh_dv racel_dv finnow finfut scghq* sclfsat1 sclfsat2 sclfsat7 sclfsato  livesp_dv cohab_dv single_dv hhtype_dv nchild_dv ndepchl_dv   scghq1_dv scghq2_dv sf12pcs_dv sf12mcs_dv  intdatd_dv intdatm_dv intdaty_dv finnow finfut fimnlabgrs_dv jbhrs jbstat jbsoc10_cc jbsic07_cc *stendreas* ccwork jbsat  country 
save datak, replace
}
foreach waves in local l  {     
    use "`datadir'/l_indresp", clear
rename l_* * 
gen str1 wave = "l"
  keep pidp wave hidp ppid dvage jbstat  qfhigh_dv racel_dv finnow finfut scghq* sclfsat1 sclfsat2 sclfsat7 sclfsato  livesp_dv cohab_dv single_dv hhtype_dv nchild_dv ndepchl_dv   scghq1_dv scghq2_dv sf12pcs_dv sf12mcs_dv  intdatd_dv intdatm_dv intdaty_dv finnow finfut fimnlabgrs_dv jbhrs jbstat jbsoc10_cc jbsic07_cc *stendreas* ccwork jbsat  country jbflex7 jbfxuse7 wkhome
   gen wfh_always = (jbfxuse7 ==1 | jbflex7 ==1 | wkhome ==7 )
  gen wfh_never = (jbfxuse7 ==0 | jbflex7 ==0 | wkhome ==1)
  gen wfh_can = (wkhome == 2 | wkhome ==3 | wkhome ==4 | wkhome ==5 | wkhome ==6)
save datal, replace
}
use"`datai'",, clear
cap drop i_* 
append using  "dataj" "datak" "datal"

save Main Survey, replace

Also, I have monthly individual wave ( 9 Months) data but only for COVID-19. This is a particular version, and they interviewed the same individuals in the " Main survey" and asked them different questions regarding Covid from April 2020 to Des 202; I append these waves by this code which we got " Covid wave" :

use data/Covid_wave_ca, clear
append using data/covid_wave_cb
append using data/Covid_wave_cc
append using data/wave_covid_cd
append using data/covid_wave_ce
append using data/Covid_wave_cf
append using data/wave_covid_cg
append using data/covid_wave_ch
append using data/Covid_wave_ci
save Covid_waves, replace

I'm not sure if there is a problem with appending the data that might cause the duplicate observations !!
I hope this is clear

[Andrew Musau]

Also, I have monthly individual wave ( 9 Months)

This explains the repeated observations, but you need to have the month variable to distinguish these.

I generated the variables "wfh_always" and "wfh_never" in the "Main survey" to extract these variables from one categorical variable ( jbfxuse7 and jbflex7), so if these variables are confusing before merging, I can drop them until we merge the two datasets, then I can generate them again.

If these are not needed, I can drop them for illustration.

* Example generated by -dataex-. For more info, type help dataex
clear
input long pidp float(month furloughed)
   76165 1 0
   76165 2 0
   76165 3 0
   76165 4 0
   76165 5 0
   76165 6 0
   76165 7 0
   76165 8 0
   76165 9 0
  280165 1 1
  280165 2 1
  280165 3 1
  280165 4 1
  280165 5 1
  469205 1 0
  469205 2 0
  469205 3 0
  599765 1 0
  599765 2 0
  599765 3 0
  599765 4 0
  732365 1 0
  732365 2 0
  732365 3 0
  732365 4 0
  732365 5 .
  732365 6 0
  732365 7 0
  732365 8 0
 1587125 1 .
 1587125 2 0
 1587125 3 0
 1587125 4 0
 1587125 5 0
 1587125 6 0
 1587125 7 0
 1587125 8 0
 1587125 9 0
 3424485 1 .
 3424485 2 0
 3424485 3 0
 3424485 4 0
 4849085 1 0
 4849085 2 0
 4849085 3 0
 4849085 4 0
 4849085 5 0
 4849085 6 0
 4849085 7 0
 4849085 8 0
 4849085 9 0
68002725 1 0
68002725 2 0
68002725 3 0
68002725 4 0
68002725 5 0
68002725 6 0
68002725 7 0
68002725 8 0
68008847 1 0
68008847 2 0
68008847 3 0
68008847 4 0
68008847 5 0
68008847 6 0
68008847 7 0
68008847 8 0
68010887 1 0
68010887 2 0
68010887 3 0
68010887 4 0
68010887 5 0
68010887 6 0
68010887 7 0
68010887 8 0
68029931 1 0
68029931 2 0
68031967 1 0
68031967 2 .
68031967 3 0
68031967 4 0
68031967 5 0
68031967 6 0
68031967 7 0
68031967 8 0
68035365 1 0
68035365 2 .
68035365 3 0
68035365 4 0
68035365 5 0
68035365 6 0
68035365 7 0
68035365 8 0
68035365 9 0
68035367 1 0
68035367 2 1
68035367 3 0
68035367 4 0
68035367 5 1
68035367 6 1
end

tempfile using
save `using', replace

* Example generated by -dataex-. For more info, type help dataex
clear
input double pidp long hidp int dvage byte(jbstat jbsat) double jbhrs str1 wave float wfh_always
   22445 276841620 34  5  2   45 "k" .
   22445 277344816 33  2  4   35 "i" .
   22445 277059218 33  2  6   30 "j" 1
   22445 276637622 35  6 -8   -8 "l" 0
   29925 619024416 40  2  6   24 "i" .
   29925 618222022 43  2  5   28 "l" 0
   29925 618385220 42  2  6   28 "k" .
   29925 618630018 41  2  7   23 "j" 0
   76165 141045620 36  5  5   35 "k" .
   76165 141460418 35  2  5   35 "j" 0
   76165 141657616 34  2  6    7 "i" .
   76165 140712422 37  2  5   35 "l" 0
  280165 754371618 39  2  5   40 "j" 1
  280165 753800422 41  1 -8   -8 "l" 0
  280165 754113220 40  2  5   38 "k" .
  280165 754793216 38  2  5   40 "i" .
  333205 414800018 28  2  6   40 "j" 0
  333205 414473620 29  2  6   37 "k" .
  333205 415106696 26  2  6   37 "i" .
  469205 415059096 27  5  5   42 "i" .
  469205 414738818 28  2  7   16 "j" 0
  469205 414412420 29  5  6   16 "k" .
  469205 414188022 30  2  6   16 "l" 0
  599765 210528700 32  2  6 36.5 "k" .
  599765 210881618 31  2  6 36.5 "j" 0
  599765 210167622 33  2  6 36.5 "l" 0
  599765 211282816 30  2  6 37.5 "i" .
  665045 210188022 38  2  4   20 "l" 0
  665045 210548420 37  2  3   20 "k" .
  665045 210902018 36  2  4   15 "j" 0
  732365 618698020 34  8 -8   -8 "k" .
  732365 619371216 32  8 -8   -8 "i" .
  732365 618949618 33  8 -8   -8 "j" 0
  732365 618507622 35  8 -8   -8 "l" 0
  760925 210188020 38  3 -7   -8 "k" .
  760925 210514418 37  8 -8   -8 "j" 0
 1587125 618269616 51  2  5   -8 "i" .
 1587125 617487622 54  1  5   -8 "l" 0
 1587125 617895618 52  1  3   -8 "j" 0
 1587125 617671220 53  1  3   -8 "k" .
 1697285 210364818 45  2  5 37.5 "j" 0
 1697285 210766016 44  2  5 37.5 "i" .
 2626845 822997216 39  2  7   20 "i" .
 2626845 822589218 40  2 -7   35 "j" 0
 2888645  76479616 28  2  6   38 "i" .
 2888645  76126018 29  2  6   38 "j" 0
 2888645  75085622 31  2 -8   -8 "l" 0
 3229325 551051618 49  3 -8   -8 "j" 0
 3229325 550711620 50  6 -8   -8 "k" .
 3424485  73521620 83  4 -8   -8 "k" .
 3424485  73195222 84  4 -8   -8 "l" 0
 3424485  73902418 82  4 -7   -8 "j" 0
 3667245 279602420 31  2  6   40 "k" .
 3667245 279942418 30  2  7   40 "j" 0
 3667245 280309616 29  2  4   40 "i" .
 3705325 144636018 64  4 -8   -8 "j" 0
 3705325 143697622 66  4 -8   -8 "l" 0
 3705325 144126020 65 97 -8   -8 "k" .
 3705325 144867216 63  4 -8   -8 "i" .
 4454005 481759622 75  4 -8   -8 "l" 0
 4454005 482840816 72  4 -8   -8 "i" .
 4454005 482106420 74  4 -8   -8 "k" .
 4454005 482446418 73  8 -8   -8 "j" 0
 4849085 346602822 37  2  3 37.5 "l" 1
 4849085 346990420 36  2  2 37.5 "k" .
 4849085 347554816 34  2  2 37.5 "i" .
 4849085 347255618 35  2  1 37.5 "j" 1
 4853165 824289216 47  2  5   37 "i" .
 4853165 823187622 50  2  7   40 "l" 0
 4853165 823500420 49  2  5   37 "k" .
 4853165 823820018 48  2  6   37 "j" 0
68002725  73025216 63  4 -8   -8 "i" .
68002725  72120822 66  4 -8   -8 "l" 0
68002725  72420020 65  4 -8   -8 "k" .
68002725  72739618 64  4 -8   -8 "j" 0
68004087  68006818 68  4 -8   -8 "j" 0
68004087  68006816 67  2  3   35 "i" .
68006127  68013618 48  3 -8   -8 "j" 0
68006127  68013620 49  3 -8   -8 "k" .
68006127  68013616 47  6 -8   -8 "i" .
68008847  68027220 61  2  7   39 "k" .
68008847  68040818 60  2  7   39 "j" 0
68008847  68040816 59  2 -8   -8 "i" .
68008847  68013622 62  2  7   39 "l" 0
68009527  68034020 41  2  5   39 "k" .
68009527  68047618 40  2  4 36.5 "j" 0
68009527  68020422 42  2  5 36.5 "l" 0
68009527  68047616 39  2  5   37 "i" .
68010887  68040820 55  2  6   32 "k" .
68010887  68027222 56  2  6   32 "l" 0
68010887  68054418 54  2  6   32 "j" 0
68010887  68054416 53  2  6   32 "i" .
68011567  68061216 43  2  6   50 "i" .
68011567  68061218 44  2  6 37.5 "j" 1
68014287  68054420 50 97 -8   -8 "k" .
68014287  68068018 49 97 -8   -8 "j" 0
68020407  68095218 81  4 -8   -8 "j" 0
68020407  68095216 80  4 -8   -8 "i" .
68020407  68081620 82  4 -8   -8 "k" .
68020564  68013618 47  3 -8   -8 "j" 0
end
label values dvage i_dvage
label values jbstat i_jbstat
label def i_jbstat 1 "Self employed", modify
label def i_jbstat 2 "Paid employment(ft/pt)", modify
label def i_jbstat 3 "Unemployed", modify
label def i_jbstat 4 "Retired", modify
label def i_jbstat 5 "On maternity leave", modify
label def i_jbstat 6 "Family care or home", modify
label def i_jbstat 8 "LT sick or disabled", modify
label def i_jbstat 97 "Doing something else", modify
label values jbsat i_jbsat
label def i_jbsat -8 "inapplicable", modify
label def i_jbsat -7 "proxy", modify
label def i_jbsat 1 "completely dissatisfied", modify
label def i_jbsat 2 "mostly dissatisfied", modify
label def i_jbsat 3 "somewhat dissatisfied", modify
label def i_jbsat 4 "neither satisfied or dissatisfied", modify
label def i_jbsat 5 "somewhat satisfied", modify
label def i_jbsat 6 "mostly satisfied", modify
label def i_jbsat 7 "completely satisfied", modify
label values jbhrs i_jbhrs
label def i_jbhrs -8 "inapplicable", modify

joinby pidp using `using'
order pidp month

Note that joinby by default keeps matches. You can specify other options to keep unmatched observations. Results in next post.

[Andrew Musau]

. list, sepby(pidp)

     +-------------------------------------------------------------------------------------------------------------------------------------+
     |     pidp   month        hidp   dvage                   jbstat                     jbsat          jbhrs   wave   wfh_al~s   furlou~d |
     |-------------------------------------------------------------------------------------------------------------------------------------|
  1. |    76165       6   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  2. |    76165       2   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  3. |    76165       3   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  4. |    76165       7   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  5. |    76165       1   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  6. |    76165       4   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  7. |    76165       8   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  8. |    76165       5   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
  9. |    76165       9   141045620      36       On maternity leave        somewhat satisfied             35      k          .          0 |
 10. |    76165       9   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 11. |    76165       2   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 12. |    76165       1   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 13. |    76165       7   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 14. |    76165       4   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 15. |    76165       3   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 16. |    76165       8   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 17. |    76165       6   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 18. |    76165       5   141460418      35   Paid employment(ft/pt)        somewhat satisfied             35      j          0          0 |
 19. |    76165       3   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 20. |    76165       7   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 21. |    76165       1   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 22. |    76165       5   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 23. |    76165       9   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 24. |    76165       8   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 25. |    76165       4   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 26. |    76165       6   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 27. |    76165       2   141657616      34   Paid employment(ft/pt)          mostly satisfied              7      i          .          0 |
 28. |    76165       2   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 29. |    76165       8   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 30. |    76165       9   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 31. |    76165       1   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 32. |    76165       3   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 33. |    76165       6   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 34. |    76165       4   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 35. |    76165       5   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
 36. |    76165       7   140712422      37   Paid employment(ft/pt)        somewhat satisfied             35      l          0          0 |
     |-------------------------------------------------------------------------------------------------------------------------------------|
 37. |   280165       1   754371618      39   Paid employment(ft/pt)        somewhat satisfied             40      j          1          1 |
 38. |   280165       5   754371618      39   Paid employment(ft/pt)        somewhat satisfied             40      j          1          1 |
 39. |   280165       2   754371618      39   Paid employment(ft/pt)        somewhat satisfied             40      j          1          1 |
 40. |   280165       3   754371618      39   Paid employment(ft/pt)        somewhat satisfied             40      j          1          1 |
 41. |   280165       4   754371618      39   Paid employment(ft/pt)        somewhat satisfied             40      j          1          1 |
 42. |   280165       3   753800422      41            Self employed              inapplicable   inapplicable      l          0          1 |
 43. |   280165       4   753800422      41            Self employed              inapplicable   inapplicable      l          0          1 |
 44. |   280165       5   753800422      41            Self employed              inapplicable   inapplicable      l          0          1 |
 45. |   280165       1   753800422      41            Self employed              inapplicable   inapplicable      l          0          1 |
 46. |   280165       2   753800422      41            Self employed              inapplicable   inapplicable      l          0          1 |
 47. |   280165       2   754113220      40   Paid employment(ft/pt)        somewhat satisfied             38      k          .          1 |
 48. |   280165       3   754113220      40   Paid employment(ft/pt)        somewhat satisfied             38      k          .          1 |
 49. |   280165       4   754113220      40   Paid employment(ft/pt)        somewhat satisfied             38      k          .          1 |
 50. |   280165       1   754113220      40   Paid employment(ft/pt)        somewhat satisfied             38      k          .          1 |
 51. |   280165       5   754113220      40   Paid employment(ft/pt)        somewhat satisfied             38      k          .          1 |
 52. |   280165       1   754793216      38   Paid employment(ft/pt)        somewhat satisfied             40      i          .          1 |
 53. |   280165       3   754793216      38   Paid employment(ft/pt)        somewhat satisfied             40      i          .          1 |
 54. |   280165       4   754793216      38   Paid employment(ft/pt)        somewhat satisfied             40      i          .          1 |
 55. |   280165       5   754793216      38   Paid employment(ft/pt)        somewhat satisfied             40      i          .          1 |
 56. |   280165       2   754793216      38   Paid employment(ft/pt)        somewhat satisfied             40      i          .          1 |
     |-------------------------------------------------------------------------------------------------------------------------------------|
 57. |   469205       3   415059096      27       On maternity leave        somewhat satisfied             42      i          .          0 |
 58. |   469205       1   415059096      27       On maternity leave        somewhat satisfied             42      i          .          0 |
 59. |   469205       2   415059096      27       On maternity leave        somewhat satisfied             42      i          .          0 |
 60. |   469205       1   414738818      28   Paid employment(ft/pt)      completely satisfied             16      j          0          0 |
 61. |   469205       3   414738818      28   Paid employment(ft/pt)      completely satisfied             16      j          0          0 |
 62. |   469205       2   414738818      28   Paid employment(ft/pt)      completely satisfied             16      j          0          0 |
 63. |   469205       2   414412420      29       On maternity leave          mostly satisfied             16      k          .          0 |
 64. |   469205       1   414412420      29       On maternity leave          mostly satisfied             16      k          .          0 |
 65. |   469205       3   414412420      29       On maternity leave          mostly satisfied             16      k          .          0 |
 66. |   469205       2   414188022      30   Paid employment(ft/pt)          mostly satisfied             16      l          0          0 |
 67. |   469205       3   414188022      30   Paid employment(ft/pt)          mostly satisfied             16      l          0          0 |
 68. |   469205       1   414188022      30   Paid employment(ft/pt)          mostly satisfied             16      l          0          0 |
     |-------------------------------------------------------------------------------------------------------------------------------------|
                                                                        TRUNCATED
  |-------------------------------------------------------------------------------------------------------------------------------------|
233. | 68008847       5    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
234. | 68008847       1    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
235. | 68008847       8    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
236. | 68008847       3    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
237. | 68008847       6    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
238. | 68008847       7    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
239. | 68008847       4    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
240. | 68008847       2    68027220      61   Paid employment(ft/pt)      completely satisfied             39      k          .          0 |
241. | 68008847       1    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
242. | 68008847       3    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
243. | 68008847       5    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
244. | 68008847       2    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
245. | 68008847       6    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
246. | 68008847       7    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
247. | 68008847       4    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
248. | 68008847       8    68040818      60   Paid employment(ft/pt)      completely satisfied             39      j          0          0 |
249. | 68008847       1    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
250. | 68008847       8    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
251. | 68008847       2    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
252. | 68008847       7    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
253. | 68008847       4    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
254. | 68008847       6    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
255. | 68008847       5    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
256. | 68008847       3    68040816      59   Paid employment(ft/pt)              inapplicable   inapplicable      i          .          0 |
257. | 68008847       5    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
258. | 68008847       4    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
259. | 68008847       8    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
260. | 68008847       6    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
261. | 68008847       2    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
262. | 68008847       1    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
263. | 68008847       3    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
264. | 68008847       7    68013622      62   Paid employment(ft/pt)      completely satisfied             39      l          0          0 |
     |-------------------------------------------------------------------------------------------------------------------------------------|
265. | 68010887       2    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
266. | 68010887       8    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
267. | 68010887       7    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
268. | 68010887       4    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
269. | 68010887       5    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
270. | 68010887       1    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
271. | 68010887       6    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
272. | 68010887       3    68040820      55   Paid employment(ft/pt)          mostly satisfied             32      k          .          0 |
273. | 68010887       4    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
274. | 68010887       2    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
275. | 68010887       6    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
276. | 68010887       3    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
277. | 68010887       5    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
278. | 68010887       7    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
279. | 68010887       8    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
280. | 68010887       1    68027222      56   Paid employment(ft/pt)          mostly satisfied             32      l          0          0 |
281. | 68010887       2    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
282. | 68010887       8    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
283. | 68010887       6    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
284. | 68010887       4    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
285. | 68010887       7    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
286. | 68010887       1    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
287. | 68010887       5    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
288. | 68010887       3    68054418      54   Paid employment(ft/pt)          mostly satisfied             32      j          0          0 |
289. | 68010887       7    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
290. | 68010887       6    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
291. | 68010887       5    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
292. | 68010887       4    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
293. | 68010887       3    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
294. | 68010887       2    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
295. | 68010887       1    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
296. | 68010887       8    68054416      53   Paid employment(ft/pt)          mostly satisfied             32      i          .          0 |
     +-------------------------------------------------------------------------------------------------------------------------------------+

[Hassan zahey]

Thanks for your help with this problem

Just last thing to make sure that I got the correct merge

The main data has 131500 obs, and the Covid-waves has 122500 obs, so when I merge the data using "joinby," I get a total of around 465000 obs.

Is this what we expected to get? Because I got many replicated obs to the same individual.
If we are able to use " merge" instead of "joinby", Do we get the same total obs?

I try to understand the difference between using joinby and merge and how this affects the data.

[Andrew Musau]

I think you should take the time to understand your data before doing anything else. Talk to someone who is familiar with the dataset. The questions I asked in #4 and #6 are pertinent. I cannot answer them on your behalf since I lack the necessary information.