Dropping Observations of Different Years if 1 Year of Data is Missing.

[Anthony Agba]

Hello!

This is my first post, so I'm not sure if the formatting is correct, I apologize. I have a set of data that I want to clean up, and the example is after this paragraph. I want to drop the health centers that are missing values for any of the three years. For example, "1ST CHOICE HEALTHCARE, INC" has missing values for all three years, so I want to drop that observation for all three years. Also, "ACCESS COMMUNITY HEALTH CENTERS, INC" is missing a value for 2021, so I'd like to drop that observation and the other observations for 2020 and 2022 because all three years do not have complete data. How would I do that? Using "drop if missing()" wouldn't work so I'm somewhat confused. Thank you!!

* Example generated by -dataex-. For more info, type help dataex
clear
input int Year str96 HealthCenterName double SubstanceAbuse
2020 "1ST CHOICE HEALTHCARE, INC."                                          .
2021 "1ST CHOICE HEALTHCARE, INC."                                          .
2022 "1ST CHOICE HEALTHCARE, INC."                                          .
2020 "A+ FAMILY HEALTHCARE, LLC"                                            0
2021 "A+ FAMILY HEALTHCARE, LLC"                                            0
2022 "A+ FAMILY HEALTHCARE, LLC"                                            0
2020 "AAA COMPREHENSIVE HEALTHCARE, INC"                     .053361234632488
2021 "AAA COMPREHENSIVE HEALTHCARE, INC"                     .015968477810037
2022 "AAA COMPREHENSIVE HEALTHCARE, INC"                     .046076458752515
2020 "AARON E. HENRY COMMUNITY HEALTH SERVICES CENTER, INC." .005876004317064
2021 "AARON E. HENRY COMMUNITY HEALTH SERVICES CENTER, INC." .005345052856634
2022 "AARON E. HENRY COMMUNITY HEALTH SERVICES CENTER, INC."  .00463181775215
2020 "ACCESS COMMUNITY HEALTH CENTERS, INC."                                0
2021 "ACCESS COMMUNITY HEALTH CENTERS, INC."                                .
2022 "ACCESS COMMUNITY HEALTH CENTERS, INC."                                0
2020 "ACCESS COMMUNITY HEALTH NETWORK"                       .020193487628778
2021 "ACCESS COMMUNITY HEALTH NETWORK"                       .024758218405759
2022 "ACCESS COMMUNITY HEALTH NETWORK"                       .038790327302535
2020 "ACCESS FAMILY HEALTH SERVICES, INC."                   .062018646128902
2021 "ACCESS FAMILY HEALTH SERVICES, INC."                   .004959263195182
2022 "ACCESS FAMILY HEALTH SERVICES, INC."                   .010178117048346
2020 "ACCESS HEALTH LOUISIANA"                               .007984641563768
2021 "ACCESS HEALTH LOUISIANA"                               .005044938104121
2022 "ACCESS HEALTH LOUISIANA"                                .00500544948129
2020 "ACHIEVABLE FOUNDATION THE"                                            0
2021 "ACHIEVABLE FOUNDATION THE"                                            .
2022 "ACHIEVABLE FOUNDATION THE"                                            0
2020 "ADAMS COUNTY HEALTH CENTER INC"                                       .
2021 "ADAMS COUNTY HEALTH CENTER INC"                                       0
2022 "ADAMS COUNTY HEALTH CENTER INC"                                       0
2020 "ADAPT, INC"                                            .041871503644686
2021 "ADAPT, INC"                                            .121016365202412
2022 "ADAPT, INC"                                            .141179610354951
2020 "ADELANTE HEALTHCARE, INC."                                            0
2021 "ADELANTE HEALTHCARE, INC."                                            0
2022 "ADELANTE HEALTHCARE, INC."                                            0
2020 "ADVANCE COMMUNITY HEALTH"                              .014745694372444
2021 "ADVANCE COMMUNITY HEALTH"                              .010681781023489
2022 "ADVANCE COMMUNITY HEALTH"                                             0
2020 "ADVOCATES FOR A HEALTHY COMMUNITY"                     .004878124083422
2021 "ADVOCATES FOR A HEALTHY COMMUNITY"                     .008518396748065
2022 "ADVOCATES FOR A HEALTHY COMMUNITY"                                    0
2020 "AFFINIA HEALTHCARE"                                    .026885715071961
2021 "AFFINIA HEALTHCARE"                                    .032286432160804
2022 "AFFINIA HEALTHCARE"                                    .024704130813293
2020 "AFFINITY HEALTH CENTER"                                               0
2021 "AFFINITY HEALTH CENTER"                                .051863041289023
2022 "AFFINITY HEALTH CENTER"                                .032967032967033
2020 "AGAPE COMMUNITY HEALTH CENTER, INC."                                  .
2021 "AGAPE COMMUNITY HEALTH CENTER, INC."                                  .
2022 "AGAPE COMMUNITY HEALTH CENTER, INC."                                  .
2020 "AIDS ACTION COALITION OF HUNTSVILLE"                   .025944076102623
2021 "AIDS ACTION COALITION OF HUNTSVILLE"                   .020811654526535
2022 "AIDS ACTION COALITION OF HUNTSVILLE"                   .042987249544627
2020 "AJO COMMUNITY HEALTH CENTER"                           .018532818532819
2021 "AJO COMMUNITY HEALTH CENTER"                           .011371467953136
2022 "AJO COMMUNITY HEALTH CENTER"                                          .
2020 "ALABAMA REGIONAL MEDICAL SERVICES"                     .018642586658899
2021 "ALABAMA REGIONAL MEDICAL SERVICES"                     .012941461054669
2022 "ALABAMA REGIONAL MEDICAL SERVICES"                     .016482067510549
2020 "ALAMEDA COUNTY HEALTH CARE SERVICES AGENCY"            .037112905169298
2021 "ALAMEDA COUNTY HEALTH CARE SERVICES AGENCY"            .088061643150205
2022 "ALAMEDA COUNTY HEALTH CARE SERVICES AGENCY"            .132612966601179
2020 "ALBANY AREA PRIMARY HEALTH CARE, INC."                 .003713480381191
2021 "ALBANY AREA PRIMARY HEALTH CARE, INC."                 .001229094420789
2022 "ALBANY AREA PRIMARY HEALTH CARE, INC."                 .001424530413724
2020 "ALBUQUERQUE HEALTH CARE FOR THE HOMELESS"                             .
2021 "ALBUQUERQUE HEALTH CARE FOR THE HOMELESS"                             .
2022 "ALBUQUERQUE HEALTH CARE FOR THE HOMELESS"                             .
2020 "ALCONA CITIZENS FOR HEALTH, INC."                      .007827206975131
2021 "ALCONA CITIZENS FOR HEALTH, INC."                      .015754842297118
2022 "ALCONA CITIZENS FOR HEALTH, INC."                      .017683545058746
2020 "ALETHEIA HOUSE, INC."                                                 1
2021 "ALETHEIA HOUSE, INC."                                  .132969034608379
2022 "ALETHEIA HOUSE, INC."                                  .829787234042553
2020 "ALEUTIAN PRIBILOF ISLAND ASSOCIATIONS"                  .26865671641791
2021 "ALEUTIAN PRIBILOF ISLANDS ASSOCIATION, INC."                          .
2022 "ALEUTIAN PRIBILOF ISLANDS ASSOCIATION, INC."                          .
2020 "ALIVIO MEDICAL CENTER"                                                .
2021 "ALIVIO MEDICAL CENTER"                                                .
2022 "ALIVIO MEDICAL CENTER"                                                .
2020 "ALL CARE HEALTH CENTER"                                .032453976086544
2021 "ALL CARE HEALTH CENTER"                                               0
2022 "ALL CARE HEALTH CENTER"                                               0
2020 "ALL FOR HEALTH, HEALTH FOR ALL"                        .012690459567128
2021 "ALL FOR HEALTH, HEALTH FOR ALL"                        .011423692983169
2022 "ALL FOR HEALTH, HEALTH FOR ALL"                         .00999454842813
2020 "ALL-INCLUSIVE COMMUNITY HEALTH CENTER"                 .011937658892451
2021 "ALL-INCLUSIVE COMMUNITY HEALTH CENTER"                 .030704815073273
2022 "ALL-INCLUSIVE COMMUNITY HEALTH CENTER"                 .029061102831595
2020 "ALLIANCE MEDICAL CENTER, INC."                         .005360669613468
2021 "ALLIANCE MEDICAL CENTER, INC."                                        0
2022 "ALLIANCE MEDICAL CENTER, INC."                         .004298718813216
2020 "ALTA MED HEALTH SERVICES CORPORATION"                  .000322840372939
2021 "ALTA MED HEALTH SERVICES CORPORATION"                  .002231459382825
2022 "ALTA MED HEALTH SERVICES CORPORATION"                  .001538700145299
2020 "ALTAPOINTE HEALTH SYSTEMS, INC."                                      0
2021 "ALTAPOINTE HEALTH SYSTEMS, INC."                                      0
2022 "ALTAPOINTE HEALTH SYSTEMS, INC."                                      .
2020 "ALTURA CENTERS FOR HEALTH"                                            0
end

[Mustafa Ugur Karakaplan]

This code will drop the Health Center if it has at least one missing observation of Substance Abuse.

egen missing_count = total(missing(SubstanceAbuse)), by(HealthCenterName)
drop if missing_count > 0

[Andrew Musau]

To Mustafa Ugur Karakaplan's helpful code, I'd add that some units may not be observed over a 3 year period, in which case you can add:

egen missing_count = total(missing(SubstanceAbuse)), by(HealthCenterName)
bys HealthCenterName: drop if missing_count > 0 | _N!=3

Also see https://www.stata.com/support/faqs/d...ble-recording/ for a general approach to these kinds of problems.

[Anthony Agba]

To Mustafa Ugur Karakaplan's helpful code, I'd add that some units may not be observed over a 3 year period, in which case you can add:

egen missing_count = total(missing(SubstanceAbuse)), by(HealthCenterName)
bys HealthCenterName: drop if missing_count > 0 | _N!=3

Also see https://www.stata.com/support/faqs/d...ble-recording/ for a general approach to these kinds of problems.

Thank you! This cleaned it up; however, this raises another question. Since, some of the clinics may have not existed in let's say 2020 and exists in 2021 and 2022, is there a way to exclude these clinics from being deleted? And in the opposite situation?

[Andrew Musau]

I think you could extend it as follows:

egen missing_count = total(missing(SubstanceAbuse)), by(HealthCenterName)
bys HealthCenterName (Year): drop if missing_count > 0 | (_N!=3 &Year[1]==2020) | (_N!=2 &Year[1]==2021) | (_N!=1 &Year[1]==2022)

The highlighted implies if the first year is 2020, we need to observe a total of 3 years. On the other hand, if the first year is 2021, we need to observe a total of 2 years, and so on. This will thus drop groups such as

Name Year
A 2020
A 2022
B 2021
C 2020

but will not drop

Name Year
D 2022
E 2021
E 2022

[Anthony Agba]

Thank you! It worked!