Merging HIES dataset

[Fariha Zaman]

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(TERM PSU) str3 psu double HHID str3 hhid double S9EQ00 str160 S9EQ00_DESC double(S9EQ01 S9EQ02 S9EQ03 S9EQ04) long hhold
1 1 "1"  6 "006" 1001 "খাট"                                                                                  1  2 20000 20000 1006
1 1 "1"  6 "006" 1002 "আলমিরা (কাঠ/স্টিল)/ফাইল কেবিনেট"          2  .     .     . 1006
1 1 "1"  6 "006" 1003 "ড্রেসিং টেবিল"                                                      1  1  8000 10000 1006
1 1 "1"  6 "006" 1004 "ওয়ারড্রব"                                                                   2  .     .     . 1006
1 1 "1"  6 "006" 1005 "ডাইনিং টেবিল ও চেয়ার"                                     1  7 15000 22000 1006
1 1 "1"  6 "006" 1006 "শোকেস"                                                                            1  1  3000     . 1006
1 1 "1"  6 "006" 1007 "পড়ার টেবিল"                                                               2  .     .     . 1006
1 1 "1"  6 "006" 1008 "টি ট্রলি/টেবিল"                                                     2  .     .     . 1006
1 1 "1"  6 "006" 1009 "টিভি ট্রলি/টেবিল"                                               2  .     .     . 1006
1 1 "1"  6 "006" 1010 "সোফা সেট/ডিভাইন খাট"                                        2  .     .     . 1006
1 1 "1"  6 "006" 1011 "আলনা"                                                                               1  1   200     . 1006
1 1 "1"  6 "006" 1012 "ইজি/রকিং চেয়ার"                                                     2  .     .     . 1006
1 1 "1"  6 "006" 1013 "কার্পেট"                                                                      2  .     .     . 1006
1 1 "1"  6 "006" 1014 "সুকেস/জুতার র*্যাক"                                         2  .     .     . 1006
1 1 "1"  6 "006" 1015 "মিট-সেফ"                                                                        1  1  3000     . 1006
1 1 "1"  6 "006" 1016 "টেলিভিশন সেট"                                                         1  1  5000     . 1006
1 1 "1"  6 "006" 1017 "ডেস্কটপ কম্পিউটার"                                          2  .     .     . 1006
1 1 "1"  6 "006" 1018 "ল্যাপটপ কম্পিউটার"                                          2  .     .     . 1006
1 1 "1"  6 "006" 1019 "মোবাইল হ্যান্ডসেট (স্মার্টফোন)"         1  1  5000     . 1006
1 1 "1"  6 "006" 1020 "মোবাইল হ্যান্ডসেট (ফিচার/বাটন ফোন)" 1  1   500     . 1006
1 1 "1"  6 "006" 1021 "হাত ঘড়ি/ দেয়াল ঘড়ি"                                             2  .     .     . 1006
1 1 "1"  6 "006" 1022 "টিভিকার্ড"                                                                2  .     .     . 1006
1 1 "1"  6 "006" 1023 "WiFi router"                                                                                2  .     .     . 1006
1 1 "1"  6 "006" 1024 "সোলার প্যানেল"                                                      2  .     .     . 1006
1 1 "1"  6 "006" 1025 "ফ্যান (সিলিং/টেবিল)"                                          1  1   500     . 1006
1 1 "1"  6 "006" 1026 "মাইক্রোওভেন"                                                          2  .     .     . 1006
1 1 "1"  6 "006" 1027 "রেফ্রিজারেটর/ফ্রিজার"                                 1  1 10000     . 1006
1 1 "1"  6 "006" 1028 "*এয়ার কন্ডিশনার/কুলার (এসি)"                     2  .     .     . 1006
1 1 "1"  6 "006" 1029 "ওয়াসিং মেশিন"                                                         2  .     .     . 1006
1 1 "1"  6 "006" 1030 "ক্যামরা/ক্যামকর্ডার"                                    2  .     .     . 1006
1 1 "1"  6 "006" 1031 "বাই-সাইকেল"                                                               1  1  2000     . 1006
1 1 "1"  6 "006" 1032 "রিকশা/অটোরিক্সা/ইজিবাইক"                          2  .     .     . 1006
1 1 "1"  6 "006" 1033 "মোটর-সাইকেল/ স্কুটার"                                     1  1 55000 79000 1006
1 1 "1"  6 "006" 1034 "মোটর ইত্যাদি"                                                         2  .     .     . 1006
1 1 "1"  6 "006" 1035 "প্রেসার ল্যাম্প/ প্রেট্রাম্যাক্স" 2  .     .     . 1006
1 1 "1"  6 "006" 1036 "সেলাই সেশিন"                                                            2  .     .     . 1006
1 1 "1"  6 "006" 1037 "রান্না ঘরের জিনিস/কাটলারী"                      1  4  1500     . 1006
1 1 "1"  6 "006" 1038 "রান্না ঘরের জিনিস/ক্রোকারীস"                1 14  5000   600 1006
1 1 "1"  6 "006" 1039 "ওয়াটার ফিল্টার/পিওরিফাইয়ার"                 2  .     .     . 1006
1 1 "1"  6 "006" 1040 "গিজার"                                                                            2  .     .     . 1006
1 1 "1"  6 "006" 1041 "খাবার পানির জন্য টিউবওয়েল"                      2  .     .     . 1006
1 1 "1"  6 "006" 1042 "ওয়াটার পাম্প/মটর"                                               2  .     .     . 1006
1 1 "1"  6 "006" 1043 "নৌকা/ইঞ্জিনচালিত নৌকা"                                2  .     .     . 1006
1 1 "1"  9 "009" 1001 "খাট"                                                                                  1  3  2200     . 1009
1 1 "1"  9 "009" 1002 "আলমিরা (কাঠ/স্টিল)/ফাইল কেবিনেট"          1  1  2000     . 1009
1 1 "1"  9 "009" 1003 "ড্রেসিং টেবিল"                                                      1  1  1500     . 1009
1 1 "1"  9 "009" 1004 "ওয়ারড্রব"                                                                   2  .     .     . 1009
1 1 "1"  9 "009" 1005 "ডাইনিং টেবিল ও চেয়ার"                                     1 10  2500     . 1009
1 1 "1"  9 "009" 1006 "শোকেস"                                                                            1  1  3500     . 1009
1 1 "1"  9 "009" 1007 "পড়ার টেবিল"                                                               1  2  3000  3400 1009
1 1 "1"  9 "009" 1008 "টি ট্রলি/টেবিল"                                                     2  .     .     . 1009
1 1 "1"  9 "009" 1009 "টিভি ট্রলি/টেবিল"                                               2  .     .     . 1009
1 1 "1"  9 "009" 1010 "সোফা সেট/ডিভাইন খাট"                                        2  .     .     . 1009
1 1 "1"  9 "009" 1011 "আলনা"                                                                               1  2  2000     . 1009
1 1 "1"  9 "009" 1012 "ইজি/রকিং চেয়ার"                                                     2  .     .     . 1009
1 1 "1"  9 "009" 1013 "কার্পেট"                                                                      2  .     .     . 1009
1 1 "1"  9 "009" 1014 "সুকেস/জুতার র*্যাক"                                         1  1   200     . 1009
1 1 "1"  9 "009" 1015 "মিট-সেফ"                                                                        1  1  2000     . 1009
1 1 "1"  9 "009" 1016 "টেলিভিশন সেট"                                                         1  1 10000     . 1009
1 1 "1"  9 "009" 1017 "ডেস্কটপ কম্পিউটার"                                          2  .     .     . 1009
1 1 "1"  9 "009" 1018 "ল্যাপটপ কম্পিউটার"                                          2  .     .     . 1009
1 1 "1"  9 "009" 1019 "মোবাইল হ্যান্ডসেট (স্মার্টফোন)"         1  2 10000 20000 1009
1 1 "1"  9 "009" 1020 "মোবাইল হ্যান্ডসেট (ফিচার/বাটন ফোন)" 1  2   500     . 1009
1 1 "1"  9 "009" 1021 "হাত ঘড়ি/ দেয়াল ঘড়ি"                                             2  .     .     . 1009
1 1 "1"  9 "009" 1022 "টিভিকার্ড"                                                                2  .     .     . 1009
1 1 "1"  9 "009" 1023 "WiFi router"                                                                                2  .     .     . 1009
1 1 "1"  9 "009" 1024 "সোলার প্যানেল"                                                      1  1  2000     . 1009
1 1 "1"  9 "009" 1025 "ফ্যান (সিলিং/টেবিল)"                                          1  3  3000     . 1009
1 1 "1"  9 "009" 1026 "মাইক্রোওভেন"                                                          2  .     .     . 1009
1 1 "1"  9 "009" 1027 "রেফ্রিজারেটর/ফ্রিজার"                                 1  1 10000     . 1009
1 1 "1"  9 "009" 1028 "*এয়ার কন্ডিশনার/কুলার (এসি)"                     2  .     .     . 1009
1 1 "1"  9 "009" 1029 "ওয়াসিং মেশিন"                                                         2  .     .     . 1009
1 1 "1"  9 "009" 1030 "ক্যামরা/ক্যামকর্ডার"                                    2  .     .     . 1009
1 1 "1"  9 "009" 1031 "বাই-সাইকেল"                                                               2  .     .     . 1009
1 1 "1"  9 "009" 1032 "রিকশা/অটোরিক্সা/ইজিবাইক"                          2  .     .     . 1009
1 1 "1"  9 "009" 1033 "মোটর-সাইকেল/ স্কুটার"                                     2  .     .     . 1009
1 1 "1"  9 "009" 1034 "মোটর ইত্যাদি"                                                         2  .     .     . 1009
1 1 "1"  9 "009" 1035 "প্রেসার ল্যাম্প/ প্রেট্রাম্যাক্স" 2  .     .     . 1009
1 1 "1"  9 "009" 1036 "সেলাই সেশিন"                                                            1  1  3000     . 1009
1 1 "1"  9 "009" 1037 "রান্না ঘরের জিনিস/কাটলারী"                      1  3   380   505 1009
1 1 "1"  9 "009" 1038 "রান্না ঘরের জিনিস/ক্রোকারীস"                1 17  8000  1680 1009
1 1 "1"  9 "009" 1039 "ওয়াটার ফিল্টার/পিওরিফাইয়ার"                 2  .     .     . 1009
1 1 "1"  9 "009" 1040 "গিজার"                                                                            2  .     .     . 1009
1 1 "1"  9 "009" 1041 "খাবার পানির জন্য টিউবওয়েল"                      2  .     .     . 1009
1 1 "1"  9 "009" 1042 "ওয়াটার পাম্প/মটর"                                               2  .     .     . 1009
1 1 "1"  9 "009" 1043 "নৌকা/ইঞ্জিনচালিত নৌকা"                                1  1 30000     . 1009
1 1 "1" 14 "014" 1001 "খাট"                                                                                  1  1 10000     . 1014
1 1 "1" 14 "014" 1002 "আলমিরা (কাঠ/স্টিল)/ফাইল কেবিনেট"          2  .     .     . 1014
1 1 "1" 14 "014" 1003 "ড্রেসিং টেবিল"                                                      2  .     .     . 1014
1 1 "1" 14 "014" 1004 "ওয়ারড্রব"                                                                   2  .     .     . 1014
1 1 "1" 14 "014" 1005 "ডাইনিং টেবিল ও চেয়ার"                                     1  8  5000     . 1014
1 1 "1" 14 "014" 1006 "শোকেস"                                                                            1  1  8000     . 1014
1 1 "1" 14 "014" 1007 "পড়ার টেবিল"                                                               2  .     .     . 1014
1 1 "1" 14 "014" 1008 "টি ট্রলি/টেবিল"                                                     2  .     .     . 1014
1 1 "1" 14 "014" 1009 "টিভি ট্রলি/টেবিল"                                               2  .     .     . 1014
1 1 "1" 14 "014" 1010 "সোফা সেট/ডিভাইন খাট"                                        2  .     .     . 1014
1 1 "1" 14 "014" 1011 "আলনা"                                                                               1  1   200     . 1014
1 1 "1" 14 "014" 1012 "ইজি/রকিং চেয়ার"                                                     2  .     .     . 1014
1 1 "1" 14 "014" 1013 "কার্পেট"                                                                      2  .     .     . 1014
1 1 "1" 14 "014" 1014 "সুকেস/জুতার র*্যাক"                                         2  .     .     . 1014
end
label values S9EQ00 S9EQ00
label def S9EQ00 1001 "Bedstead/ Khat", modify
label def S9EQ00 1002 "Almirah (Wood/Steel)/File Cabinet", modify
label def S9EQ00 1003 "Dressing Table", modify
label def S9EQ00 1004 "Wardrobe", modify
label def S9EQ00 1005 "Dining Table and Chair", modify
label def S9EQ00 1006 "Showcase", modify
label def S9EQ00 1007 "Reading Table", modify
label def S9EQ00 1008 "Tea Trolly/Table", modify
label def S9EQ00 1009 "TV Trolly/Table", modify
label def S9EQ00 1010 "Sofa Set /Divane", modify
label def S9EQ00 1011 "Dress Stand", modify
label def S9EQ00 1012 "Easy/Rocking Chair", modify
label def S9EQ00 1013 "Carpet", modify
label def S9EQ00 1014 "Shoe Rack", modify
label def S9EQ00 1015 "Meat Safe", modify
label def S9EQ00 1016 "TV set", modify
label def S9EQ00 1017 "Desktop Computer", modify
label def S9EQ00 1018 "Laptop Computer", modify
label def S9EQ00 1019 "Mobile Handset (Smart Phone)", modify
label def S9EQ00 1020 "Mobile Handset (Feature/Button Phone)", modify
label def S9EQ00 1021 "Wrist Watch/Wall Clock", modify
label def S9EQ00 1022 "TV Card", modify
label def S9EQ00 1023 "WiFi router", modify
label def S9EQ00 1024 "Solar Panel", modify
label def S9EQ00 1025 "Fan (Seiling/Table)", modify
label def S9EQ00 1026 "Microwave/ Electric Oven", modify
label def S9EQ00 1027 "Refreigerator/Fridger", modify
label def S9EQ00 1028 "*Air Conditioner/ Cooler (AC)", modify
label def S9EQ00 1029 "Washing Machine", modify
label def S9EQ00 1030 "Camera/Camcard", modify
label def S9EQ00 1031 "Bicycle", modify
label def S9EQ00 1032 "Rikshaw/Auto-Rikshaw/Easybike", modify
label def S9EQ00 1033 "Motorcycle/Scooter", modify
label def S9EQ00 1034 "Motor Car", modify
label def S9EQ00 1035 "Pressure Lamp", modify
label def S9EQ00 1036 "Sewing Mahine", modify
label def S9EQ00 1037 "Kitchen Stuff/Cutlery", modify
label def S9EQ00 1038 "Crockeries", modify
label def S9EQ00 1039 "Water Filter/Water Purifier", modify
label def S9EQ00 1040 "Geyser", modify
label def S9EQ00 1041 "Tubewell (for drinking water)", modify
label def S9EQ00 1042 "Water Pump/Motor", modify
label def S9EQ00 1043 "Boat/Engine Boat", modify
label values S9EQ01 yesno
label def yesno 1 "Yes", modify
label def yesno 2 "No", modify

One of my data looks like this. Where household 1006 has 43 rows. I want to merge this dataset with another data set where the household 1006 has only 1 rows.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(PSU HHID S6AQ01 S6AQ02 S6AQ03 S6AQ04) long hhold
1   6 2 2 2 5 1006
1   9 4 3 1 3 1009
1  14 1 3 2 3 1014
1  28 1 2 2 3 1028
1  29 1 2 2 3 1029
1  33 2 3 2 5 1033
1  38 3 3 2 3 1038
1  45 2 3 2 3 1045
1  48 2 2 2 3 1048
1  54 1 3 1 5 1054
1  65 3 4 1 3 1065
1  67 1 3 1 5 1067
1  72 2 4 1 5 1072
1  73 1 4 1 5 1073
1  74 2 5 1 5 1074
1  75 2 3 2 5 1075
1  84 2 2 2 5 1084
1  85 2 1 2 5 1085
1 100 1 3 2 5 1100
1 109 1 4 2 5 1109
2   7 2 3 2 3 2007
2  12 2 2 2 3 2012
2  19 4 4 1 3 2019
2  28 1 3 2 3 2028
2  29 2 3 2 3 2029
2  30 1 4 2 3 2030
2  36 1 2 2 3 2036
2  38 2 4 1 3 2038
2  40 1 3 2 3 2040
2  42 3 3 2 3 2042
2  48 2 4 2 3 2048
2  54 2 1 2 3 2054
2  60 2 4 1 3 2060
2  75 2 4 2 3 2075
2  81 2 3 2 3 2081
2  91 5 3 2 3 2091
2 103 2 4 1 3 2103
2 106 1 1 2 3 2106
2 108 2 2 2 3 2108
2 115 2 4 2 3 2115
3   1 2 3 1 3 3001
3   2 2 2 1 3 3002
3   9 2 2 1 3 3009
3  21 2 2 1 3 3021
3  28 1 4 1 5 3028
3  29 2 3 1 5 3029
3  30 2 3 1 3 3030
3  37 2 3 2 3 3037
3  41 2 2 2 5 3041
3  56 2 2 2 3 3056
3  62 2 3 2 3 3062
3  68 2 4 2 3 3068
3  71 4 3 2 3 3071
3  73 2 4 2 5 3073
3  80 4 3 2 3 3080
3  90 4 4 2 3 3090
3 100 2 3 2 3 3100
3 102 4 4 1 5 3102
3 106 1 3 2 5 3106
3 118 2 3 2 3 3118
4   2 1 2 2 5 4002
4   4 2 2 1 5 4004
4  12 4 6 1 5 4012
4  28 2 4 1 5 4028
4  34 2 4 1 3 4034
4  38 2 3 2 3 4038
4  40 3 3 1 5 4040
4  42 1 2 2 3 4042
4  48 2 3 2 5 4048
4  53 2 3 2 5 4053
4  55 2 3 2 5 4055
4  61 2 3 1 5 4061
4  62 1 4 2 5 4062
4  67 2 5 1 5 4067
4  74 2 3 2 5 4074
4  78 3 3 2 5 4078
4  84 2 4 1 5 4084
4  87 2 4 2 3 4087
4  99 3 8 1 3 4099
4 101 4 5 1 5 4101
5   4 2 5 1 3 5004
5   6 1 3 1 3 5006
5  16 2 4 1 3 5016
5  19 2 3 1 3 5019
5  23 2 3 1 3 5023
5  27 1 6 1 5 5027
5  37 2 3 2 4 5037
5  45 1 3 2 3 5045
5  46 2 5 1 3 5046
5  48 1 4 1 3 5048
5  51 2 4 1 3 5051
5  59 2 5 1 3 5059
5  64 2 1 2 3 5064
5  69 2 4 1 3 5069
5  70 2 5 1 3 5070
5  76 2 6 1 3 5076
5  77 2 6 1 3 5077
5  84 2 4 1 3 5084
5 105 2 6 1 3 5105
5 106 2 6 1 3 5106
end
label values S6AQ03 S6AQ03
label def S6AQ03 1 "Yes", modify
label def S6AQ03 2 "No", modify
label values S6AQ04 S6AQ04
label def S6AQ04 3 "Tin (CI sheet)", modify
label def S6AQ04 4 "Wood", modify
label def S6AQ04 5 "Brick/Cement", modify

Whenever I try to merge them, 125 observations does not merge. (I merge them after dropping some unnecessary observation from the first dataset above) How do I merge them properly?

merge 1:1 hhold using "C:\Users\lenovo\Downloads\HH_SEC_9E"

I am using the above command to merge.
Without dropping the observation stata says: variable hhold does not uniquely identify observations in the using data
Is it possible to merge the datasets without dropping the observations? How can I do it?

[Andrew Musau]

"HHID" and "PSU" uniquely identify observations in your second dataset. Given that you want to merge with your first dataset and keep both matched and unmatched observations in the first dataset:

*LOAD 1ST DATASET
merge m:1 HHID PSU using usingfile, keep(master match) nogen

where you replace "usingfile" with the name of the second dataset.

[Fariha Zaman]

I did it but still it has 131 unmatched observations from master data.

[Andrew Musau]

How is that a problem? A match is an identifier combination present in both the master and using files. If you want to keep only matches:

merge m:1 HHID PSU using usingfile, keep(match) nogen

Otherwise, unmatched observations are an issue only if you can show that a combination exists in the master and using files, but merge is not linking them.

[Benno Schoenberger]

I did it but still it has 131 unmatched observations from master data.

Hi Fariha,
executing

distinct PSU HHID

for both datasets tells me, that with only 1 PSU and 3 HHID in dataset1 and 5 PSU and 68 HHId in dataset2 there must be unmatches observations. What exactly is your issue with that?

[Fariha Zaman]

I have to merge 2 more datasets, when I do that there are around 5500 unmatched observations. To my best knowledge it ultimately tampers my analysis.

[Fariha Zaman]

Also, if I do not drop some from the first dataset I cannot merge it with the second dataset. Stata says: variables PSU HHID do not uniquely identify observations in the using data. What should I do about it?

[Benno Schoenberger]

I would suggest to first of all read the helpfile for the merge command and to understand the difference between 1:1 and 1:m merges.

The error message can have a variety of causes.
Try to make sure that your key variables uniquely identifiy all observations in you dataset. You can inspect that by

isid PSU HHID

If the isid command produces NO errors, you can use these variables for 1:1, 1:m (as using) or m:1 (as master) merges.
In case Stata issues an error here, you have to check whether any of your key variables has some missings or more key variables have to be used.
In this case, however, I suspect that a 1:1 merge was attempted where a 1:m or m:1 merge would be necessary.

[Fariha Zaman]

I would suggest to first of all read the helpfile for the merge command and to understand the difference between 1:1 and 1:m merges.

The error message can have a variety of causes.
Try to make sure that your key variables uniquely identifiy all observations in you dataset. You can inspect that by

isid PSU HHID

If the isid command produces NO errors, you can use these variables for 1:1, 1:m (as using) or m:1 (as master) merges.
In case Stata issues an error here, you have to check whether any of your key variables has some missings or more key variables have to be used.
In this case, however, I suspect that a 1:1 merge was attempted where a 1:m or m:1 merge would be necessary.

Thank you for the suggestion, I have inspected and found out that, variables psu hhid do not uniquely identify the observations. I am not sure what should be my next step, please guide me.
Even if I try m:1 or 1:m STATA says:
variables psu hhid do not uniquely identify observations in the master data

[Benno Schoenberger]

Your dataset1 has no variables that uniquely identify each observation. However, since dataset2 can be clearly identified by PSU and HHID, an m:1 or 1:m merge can be carried out here without errors. You can find the correct code here:

merge m:1 HHID PSU using usingfile, keep(match) nogen

For merging the next datasets you then have to find key variables, that again are present in both datasets and uniquely identifiy your observations in at least one of them.

[Fariha Zaman]

Thank a lot for helping