I have separate outpatient and inpatient datasets both of which have multiple records per ID (URN_new) for a range variables such as outpatient appointments and inpatient admissions. I need to merge the two datasets so that I can do analyese involving both outpatient and inpatient service use. I thought I have to do m:m merge however there is a lot of information both in this forum and others discouraging m:m merging. Therefore, I tried joinby but I got too many records (280,071) in the joined dataset while the original datasets had only 45,750 and 29,285 records. Below are the a preview of the two datasets. I appreciate any help.
Thank you
[----------------------- Outpatient -----------------------
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long URN_new int AppointmentDate double AppointmentTime str38 AppointmentType float qdate 601 20361 -1.8934242e+12 "Diabetes Review" 222 601 20466 -1.8934242e+12 "Diabetes Review" 224 601 20611 -1.893426e+12 "Diabetes Review" 225 601 20670 -1.8934056e+12 "Diabetes Edu Group Review" 226 601 20793 -1.893426e+12 "Diabetes Review" 227 601 20947 -1893424800000.0002 "Diabetes Review" 229 601 21101 -1.8934158e+12 "Diabetes Review" 231 601 21101 -1.8934158e+12 "Diabetes Review" 231 601 21283 -1893419699999.9998 "Diabetes Review" 233 601 21283 -1893419699999.9998 "Diabetes Review" 233 601 21402 -1893419699999.9998 "Diabetes Review" 234 694 21097 -1.8934209e+12 "Diabetes Review" 231 831 21349 -1.8934173e+12 "Diabetes New" 233 831 21354 -1.8934083e+12 "Diabetes Phone Review" 233 831 21361 -1.8934092e+12 "Diabetes Review" 233 831 21361 -1.8934164e+12 "Diabetes Phone Review" 233 831 21368 -1.8934182e+12 "Diabetes Review" 234 831 21375 -1.8934272e+12 "Diabetes Phone Review" 234 831 21382 -1.8934272e+12 "Diabetes Phone Review" 234 1252 21200 -1.8934182e+12 "Diabetes Review" 232 1252 21221 -1.8934173e+12 "Diabetes Review" 232 1252 21244 -1.89342e+12 "Diabetes Review" 232 1252 21251 -1.8934209e+12 "Diabetes Phone Review" 232 1252 21347 -1.8934227e+12 "Diabetes Review" 233 1252 21361 -1.8934227e+12 "Diabetes Review" 233 1252 21383 -1.8934164e+12 "Diabetes Review" 234 1252 21474 -1.8934236e+12 "Diabetes Review" 235 1252 21510 -1.8934254e+12 "Diabetes Review" 235 1252 21524 -1.8934254e+12 "Diabetes Review" 235 1252 21528 -1.8934182e+12 "Diabetes Review" 235 1777 21440 -1.8934254e+12 "Diabetes New" 234 1777 21468 -1.8934254e+12 "Diabetes Review" 235 1777 21479 -1.8934254e+12 "Diabetes Phone Review" 235 1777 21486 -1.8934182e+12 "Diabetes Phone Review" 235 1777 21531 -1.8934083e+12 "Diabetes Phone Review" 235 2135 21222 -1.8934092e+12 "Diabetes Review" 232 2135 21222 -1.8934074e+12 "Diabetes Review" 232 2135 21250 -1.893402e+12 "Diabetes Review" 232 2151 20380 -1.8934002e+12 "Diabetes Review" 223 2151 20464 -1.893408e+12 "Diabetes Review" 224 2151 20604 -1.893408e+12 "Diabetes Review" 225 2151 20695 -1.8934068e+12 "Diabetes Review" 226 2602 20590 -1.8934002e+12 "Diabetes Edu Review" 225 3174 20474 -1.8934188e+12 "Diabetes Edu Phone Review" 224 3174 20527 -1.893408e+12 "Diabetes New" 224 3174 20555 -1.893408e+12 "Diabetes New" 225 3174 20555 -1.893408e+12 "Diabetes New" 225 3174 20555 -1.893408e+12 "Diabetes New" 225 3174 20555 -1.893408e+12 "Diabetes New" 225 3205 21353 -1.893408e+12 "Diabetes New" 233 3205 21430 -1.893426e+12 "Diabetes New" 234 3205 21430 -1.8934182e+12 "Diabetes Edu Review" 234 3205 21465 -1.8934236e+12 "Diabetes Phone Review" 235 3205 21486 -1.8934236e+12 "Diabetes Phone Review" 235 3205 21486 -1.8934236e+12 "Diabetes Phone Review" 235 3517 20424 -1893416999999.9998 "Diabetes Review" 223 3595 20555 -1.893426e+12 "Diabetes New" 225 3595 20555 -1.893426e+12 "Diabetes New" 225 3595 20555 -1.8934164e+12 "Diabetes Edu Review" 225 3595 20555 -1.8934164e+12 "Diabetes Edu Review" 225 3595 20585 -1.8934074e+12 "Diabetes Edu New" 225 3595 20592 -1.8934056e+12 "Diabetes Edu Review" 225 3595 20626 -1.8934236e+12 "Diabetes Edu New" 225 3595 20627 -1.8934092e+12 "Diabetes Edu Review" 225 3595 20629 -1.8934074e+12 "Diabetes Edu Review" 225 3595 20634 -1.8934236e+12 "Diabetes Edu Review" 225 3595 20642 -1.8934236e+12 "Diabetes Edu Review" 226 3595 20647 -1.8934254e+12 "Diabetes Edu Phone Review" 226 3595 20650 -1.8934254e+12 "Diabetes Edu Phone Review" 226 3595 20653 -1.8934179e+12 "Diabetes Review" 226 3595 20654 -1.8934254e+12 "Diabetes Edu Phone Review" 226 3595 20657 -1.8934254e+12 "Diabetes Edu Phone Review" 226 3595 20744 -1.8934179e+12 "Diabetes Review" 227 3595 20759 -1.8934164e+12 "Diabetes Edu Review" 227 3595 20772 -1.8934233e+12 "Diabetes Review" 227 3595 20779 -1.893426e+12 "Diabetes Review" 227 3595 20863 -1.8934002e+12 "Diabetes Review" 228 3595 20870 -1.893426e+12 "Diabetes Review" 228 3595 20870 -1.8934182e+12 "Diabetes Edu Review" 228 3595 20878 -1.8934092e+12 "Diabetes Edu Review" 228 3595 20885 -1.8934092e+12 "Diabetes Edu Review" 228 3595 20892 -1.8934092e+12 "Diabetes Edu Review" 228 3595 20899 -1.8934092e+12 "Diabetes Edu Review" 228 3595 20906 -1.8934068e+12 "Diabetes Edu Review" 228 3595 20927 -1.8934236e+12 "Diabetes Edu Review" 229 3595 20937 -1893411599999.9998 "Diabetes Edu Phone Review" 229 3595 20943 -1.8934236e+12 "Diabetes Edu Review" 229 3595 20950 -1893414000000.0002 "Diabetes Edu Review" 229 3595 20957 -1893414000000.0002 "Diabetes Edu Review" 229 3595 20964 -1.8934182e+12 "Diabetes Edu Review" 229 3595 20971 -1.8934164e+12 "Diabetes Edu Review" 229 3595 20978 -1.8934164e+12 "Diabetes Edu Review" 229 3595 20985 -1893414000000.0002 "Diabetes Edu Review" 229 3595 20990 -1.8934236e+12 "Diabetes Edu Review" 229 3595 21013 -1.8934212e+12 "Diabetes Edu Review" 230 3595 21066 -1893419699999.9998 "Diabetes Review" 230 3595 21124 -1.8934164e+12 "Diabetes Edu Review" 231 3595 21132 -1893414000000.0002 "Diabetes Edu Review" 231 3595 21139 -1.8934164e+12 "Diabetes Edu Review" 231 3595 21146 -1893414000000.0002 "Diabetes Edu Review" 231 end format %tddd-Mon-YY AppointmentDate format %tchh:MM AppointmentTime format %tq qdate
][/----------------------- Inpatient -----------------------
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double URN_new int(AdmitDate DischargeDate LengthOfStay) double(ALOS TotalCost) float admissionGap str4 DRGcode long(AdmitSource PrincipalDiagnosis) 578 21176 21201 25 4.38 6643.2588894 5 "U61B" 43 543 578 20530 20530 1 1 797.86199336 646 "U60Z" 41 543 578 21206 21218 12 4.38 6643.2588894 228 "U61B" 43 543 578 21446 21452 6 4.38 6643.2588894 . "U61B" 43 543 694 20855 20963 108 36.57 155508.65399 48 "A14A" 24 80 694 21011 21012 1 1.43 1988.1031409 27 "I76B" 34 1638 694 21039 21049 10 5.14 25622.725337 . "I15Z" 34 1638 831 21258 21258 1 3.18 3823.5879914 138 "T61B" 3 490 831 21396 21396 1 2.98 4573.7466533 5 "Z64A" 3 422 831 21633 21634 1 1.47 1948.7668597 . "Z61B" 3 427 831 21621 21621 1 1.61 1993.8071389 12 "I68B" 3 294 831 21401 21405 4 2.48 4082.6048197 216 "K60B" 3 63 831 21166 21166 1 . . 92 "I79C" 3 308 1252 21495 21495 1 1.62 2357.5703669 . "L64A" 3 320 1538 20913 20922 9 23.33 62502.49338 262 "G02A" 7 123 1538 20478 20481 3 5.77 15350.511009 0 "I08C" 37 1988 1538 20746 20746 1 1.2 1593.6014406 12 "Z64B" 35 2203 1538 20786 20786 1 1 1765.2271336 2 "R63Z" 35 2219 1538 20636 20636 1 . . 108 "L41Z" 56 1650 1538 20798 20807 9 4.8 9747.7298418 5 "Q60A" 35 365 1538 20481 20506 25 8.65 12021.43979 120 "I78A" 37 1988 1538 20758 20758 1 1 1765.2271336 2 "R63Z" 35 2219 1538 20760 20760 1 1.2 1593.6014406 12 "Z64B" 35 2203 1538 20774 20774 1 1.2 1593.6014406 12 "Z64B" 35 2203 1538 20744 20744 1 1 1765.2271336 2 "R63Z" 35 2219 1538 20626 20626 1 1.07 2216.9870465 10 "G48B" 16 273 1538 20812 20817 5 7.31 15632.140792 96 "G12B" 35 123 1538 21184 21184 1 1.35 1556.449996 . "G70C" 13 1203 1538 20772 20772 1 1 1765.2271336 2 "R63Z" 35 2219 1538 20788 20788 1 1.2 1593.6014406 10 "Z64B" 35 2203 1777 21372 21402 30 36.57 155508.65399 . "A14A" 3 188 2022 20340 20341 1 3.55 5716.6510998 4 "B71A" 35 665 2022 21705 21706 1 2.69 3876.6021616 . "E62B" 35 1005 2022 20739 20741 2 1.96 2867.4517282 388 "T62B" 35 1815 2022 20565 20566 1 4.4 6316.848872 173 "I71A" 35 1459 2022 20480 20482 2 6.97 11022.318042 25 "J69A" 35 148 2022 20360 20360 1 5.76 8706.9041793 13 "I76A" 13 1453 2022 20354 20354 1 5.73 11252.091114 6 "I12C" 37 1622 2022 20394 20394 1 1 1765.2271336 21 "R63Z" 35 2219 2022 21350 21350 1 1.79 3099.418148 3 "E63A" 29 644 2022 20436 20436 1 1 1765.2271336 21 "R63Z" 35 2219 2022 21129 21131 2 5.57 8662.3784051 65 "G70A" 35 1765 2022 21475 21475 1 1.05 2629.9460853 230 "J11B" 39 155 2022 20415 20415 1 1 1765.2271336 21 "R63Z" 35 2219 2022 20373 20373 1 1 1765.2271336 21 "R63Z" 35 2219 2022 21353 21357 4 6.21 10127.665202 118 "B81A" 29 671 2022 21196 21199 3 1.35 1556.449996 30 "G70C" 35 1765 2022 20457 20457 1 1 1765.2271336 23 "R63Z" 35 2219 2022 21229 21230 1 14.08 23560.38022 120 "I12B" 37 1622 2022 20345 20345 1 1 1765.2271336 9 "R63Z" 35 2219 2022 20507 20512 5 4.4 6316.848872 44 "I71A" 35 1456 2022 20556 20559 3 2.62 4184.1305553 6 "E67A" 35 1750 2022 20345 20346 1 6.24 6989.0168626 -1 "I64B" 35 1623 2151 20590 20597 7 6.03 17136.220585 . "H08A" 21 1260 2368 20418 20419 1 1.1 5817.8406347 18 "J07Z" 2 171 2368 20507 20507 1 1 1765.2271336 21 "R63Z" 35 2219 2368 21301 21301 1 1.05 1637.1067305 3 "Z40Z" 14 2165 2368 20465 20465 1 1 1765.2271336 21 "R63Z" 35 2219 2368 20486 20486 1 1 1765.2271336 21 "R63Z" 35 2219 2368 20437 20438 1 1.1 5817.8406347 27 "J07Z" 2 171 2368 21304 21306 2 1.32 7467.1064286 . "K05B" 5 309 2368 20528 20528 1 1 1765.2271336 773 "R63Z" 35 2219 2539 21257 21260 3 3.62 6159.4472067 . "B70C" 3 167 2662 21206 21209 3 4.36 9307.1230509 5 "K64A" 10 382 2662 21131 21131 1 2.47 3466.9572444 75 "G70B" 13 1196 2662 21214 21216 2 1.59 10095.88993 . "K06B" 5 382 2991 21169 21171 2 5.65 8372.365315 0 "E65A" 47 1041 2991 20916 20916 1 2.82 5079.7051888 253 "C63A" 29 72 2991 21183 21184 1 4.08 6578.26522 167 "F66A" 29 831 2991 21358 21370 12 6.29 13499.354549 . "E41B" 18 1041 2991 21171 21173 2 2.98 4573.7466533 10 "Z64A" 47 2242 2991 21351 21354 3 5.65 8372.365315 4 "E65A" 47 1041 3517 20636 20662 26 7.13 21543.20818 0 "F10A" 6 836 3517 20717 20720 3 2.04 3278.3468133 . "H64B" 16 1266 3517 20495 20495 1 1.05 1637.1067305 141 "Z40Z" 16 2169 3517 20662 20711 43 12.41 21350.892592 6 "B70A" 17 906 3620 21538 21538 1 1.23 1327.5986064 . "X60B" 13 1883 3620 21460 21460 1 1.3 1527.7909203 78 "D63B" 14 1011 4013 20510 20513 3 2.78 3764.075321 848 "E65B" 18 1041 4013 21548 21552 4 2.78 3764.075321 . "E65B" 28 1041 4013 21427 21427 1 1.02 1701.8407983 96 "C03B" 36 418 4013 21361 21361 1 1.02 1701.8407983 37 "C03B" 36 418 4013 21523 21523 1 1.02 1701.8407983 25 "C03B" 36 418 4013 21398 21398 1 1.02 1701.8407983 29 "C03B" 36 418 4232 21608 21609 1 9.23 15999.218575 . "B66A" 47 209 4232 21376 21382 6 12.74 29432.827589 2 "E02A" 28 144 4232 21456 21456 1 1 1765.2271336 26 "R63Z" 35 2219 4232 21594 21605 11 9.23 15999.218575 1 "B66A" 35 209 4232 21606 21608 2 9.23 15999.218575 0 "B66A" 47 209 4232 21434 21434 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21435 21435 1 1 1765.2271336 19 "R63Z" 35 2219 4232 21405 21431 26 6.33 11153.920754 2 "K62A" 47 493 4232 21454 21454 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21455 21455 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21483 21483 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21482 21482 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21433 21433 1 1 1765.2271336 1 "R63Z" 35 2219 4232 21484 21484 1 1 1765.2271336 110 "R63Z" 35 2219 4232 21384 21388 4 8.12 13030.851629 17 "E71A" 47 144 4342 21327 21327 1 1.83 2208.8979991 35 "I72B" 3 304 end format %tdnn/dd/CCYY AdmitDate format %tdnn/dd/CCYY DischargeDate label values AdmitSource AdmitSource label def AdmitSource 2 "B&E", modify label def AdmitSource 3 "BIRD", modify label def AdmitSource 5 "BNE", modify label def AdmitSource 6 "CARD", modify label def AdmitSource 7 "COLO", modify label def AdmitSource 10 "DEND", modify label def AdmitSource 13 "EMER", modify label def AdmitSource 14 "ENT", modify label def AdmitSource 16 "GAST", modify label def AdmitSource 17 "GERI", modify label def AdmitSource 18 "GMAP", modify label def AdmitSource 21 "HPB", modify label def AdmitSource 24 "INFD", modify label def AdmitSource 28 "MED3", modify label def AdmitSource 29 "MED4", modify label def AdmitSource 34 "NSUR", modify label def AdmitSource 35 "OHU", modify label def AdmitSource 36 "OPHT", modify label def AdmitSource 37 "ORTH", modify label def AdmitSource 39 "PLAS", modify label def AdmitSource 41 "PSY", modify label def AdmitSource 43 "PSYE", modify label def AdmitSource 47 "RESP", modify label def AdmitSource 56 "UROL", modify label values PrincipalDiagnosis PrincipalDiagnosis label def PrincipalDiagnosis 63 "B07", modify label def PrincipalDiagnosis 72 "B30.9", modify label def PrincipalDiagnosis 80 "B37.6", modify label def PrincipalDiagnosis 123 "C18.7", modify label def PrincipalDiagnosis 144 "C34.3", modify label def PrincipalDiagnosis 148 "C43.4", modify label def PrincipalDiagnosis 155 "C44.3", modify label def PrincipalDiagnosis 167 "C49.3", modify label def PrincipalDiagnosis 171 "C50.4", modify label def PrincipalDiagnosis 188 "C71.2", modify label def PrincipalDiagnosis 209 "C79.3", modify label def PrincipalDiagnosis 273 "D12.0", modify label def PrincipalDiagnosis 294 "D22.5", modify label def PrincipalDiagnosis 304 "D32.0", modify label def PrincipalDiagnosis 308 "D35.0", modify label def PrincipalDiagnosis 309 "D35.1", modify label def PrincipalDiagnosis 320 "D41.4", modify label def PrincipalDiagnosis 365 "D70", modify label def PrincipalDiagnosis 382 "E04.2", modify label def PrincipalDiagnosis 418 "E11.33", modify label def PrincipalDiagnosis 422 "E11.42", modify label def PrincipalDiagnosis 427 "E11.64", modify label def PrincipalDiagnosis 490 "E80.6", modify label def PrincipalDiagnosis 493 "E83.5", modify label def PrincipalDiagnosis 543 "F20.0", modify label def PrincipalDiagnosis 644 "G47.32", modify label def PrincipalDiagnosis 665 "G62.9", modify label def PrincipalDiagnosis 671 "G72.0", modify label def PrincipalDiagnosis 831 "I20.9", modify label def PrincipalDiagnosis 836 "I21.4", modify label def PrincipalDiagnosis 906 "I63.4", modify label def PrincipalDiagnosis 1005 "J18.9", modify label def PrincipalDiagnosis 1011 "J32.0", modify label def PrincipalDiagnosis 1041 "J44.0", modify label def PrincipalDiagnosis 1196 "K57.30", modify label def PrincipalDiagnosis 1203 "K59.0", modify label def PrincipalDiagnosis 1260 "K80.00", modify label def PrincipalDiagnosis 1266 "K80.40", modify label def PrincipalDiagnosis 1453 "M25.46", modify label def PrincipalDiagnosis 1456 "M25.53", modify label def PrincipalDiagnosis 1459 "M25.57", modify label def PrincipalDiagnosis 1622 "M87.15", modify label def PrincipalDiagnosis 1623 "M87.16", modify label def PrincipalDiagnosis 1638 "M95.2", modify label def PrincipalDiagnosis 1650 "N13.1", modify label def PrincipalDiagnosis 1750 "R06.0", modify label def PrincipalDiagnosis 1765 "R11", modify label def PrincipalDiagnosis 1815 "R50.9", modify label def PrincipalDiagnosis 1883 "S19.9", modify label def PrincipalDiagnosis 1988 "S72.2", modify label def PrincipalDiagnosis 2165 "Z01.8", modify label def PrincipalDiagnosis 2169 "Z08.0", modify label def PrincipalDiagnosis 2203 "Z45.1", modify label def PrincipalDiagnosis 2219 "Z51.1", modify label def PrincipalDiagnosis 2242 "Z75.2", modify
Listed 100 out of 45750 observations
Use the count() option to list more
.
end of do-file
.
]
0 Response to Many to many merge
Post a Comment