Reshaping a subset of variables from long to wide

[Katie Tseng]

Hello,

I have a hospital data file with 8,199 observations and 25 variables. I would like to reshape the variable antimicrobialagent from long to wide format on patientcode. The following code produced a reshape error, which I'm having trouble working around. Any help would be greatly appreciated.

Thank you,

Katie

reshape wide interpretedresult, i(patientcode organism) j(antimicrobialagent) str

Here is a sample of the original data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long patientcode str30 organism str25 antimicrobialagent str2 interpretedresult
84502 "ESCHERICHIA COLI"            "AMPICILLIN"               "R "
84502 "ESCHERICHIA COLI"            "GENTAMICIN"               "S"
84502 "ESCHERICHIA COLI"            "COTRIMOXAZOLE"            "R"
84502 "ESCHERICHIA COLI"            "AMOXYCILLIN-CLAVULANATE"  "R"
84502 "ESCHERICHIA COLI"            "PIPERACILLIN/TAZOBACTAM " "S"
84502 "ESCHERICHIA COLI"            "Ciprofloxacin"            "R"
84502 "ESCHERICHIA COLI"            "CEFUROXIME"               "R"
84502 "ESCHERICHIA COLI"            "CEFTRIAXONE"              "R"
84502 "ESCHERICHIA COLI"            "CEFOPERAZONE-SULBACTAM"   "S"
84502 "ESCHERICHIA COLI"            "AMIKACIN"                 "I"
84502 "ESCHERICHIA COLI"            "IMIPENEM"                 "S"
84502 "ESCHERICHIA COLI"            "ERTAPENEM"                "S"
84502 "ESCHERICHIA COLI"            "CEFUROXIME-AXETIL"        "R"
84502 "ESCHERICHIA COLI"            "CEFEPIME"                 "R"
84502 "ESCHERICHIA COLI"            "CEFOTAXIME"               "R"
84502 "ESCHERICHIA COLI"            "MEROPENEM"                "S"
84502 "ESCHERICHIA COLI"            "NALIDIXIC ACID"           "R"
84502 "ESCHERICHIA COLI"            "TIGECYCLINE"              "S"
84502 "ESCHERICHIA COLI"            "COLISTIN"                 "S"
91072 "KLEBSIELLA PNEUMONIAE"       "AMPICILLIN "              "R"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFAZOLIN "               "S"
91072 "KLEBSIELLA PNEUMONIAE"       "GENTAMICIN "              "S"
91072 "KLEBSIELLA PNEUMONIAE"       "TOBRAMYCIN "              "S"
91072 "KLEBSIELLA PNEUMONIAE"       "COTRIMOXAZOLE"            "S"
91072 "KLEBSIELLA PNEUMONIAE"       "AMOXYCILLIN-CLAVULANATE"  "S"
91072 "KLEBSIELLA PNEUMONIAE"       "PIPERACILLIN "            "R"
91072 "KLEBSIELLA PNEUMONIAE"       "PIPERACILLIN/TAZOBACTAM " "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CIPROFLOXACIN "           "S"
91072 "KLEBSIELLA PNEUMONIAE"       "LEVOFLOXACIN "            "S"
91072 "KLEBSIELLA PNEUMONIAE"       "MOXIFLOXACIN "            "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFUROXIME "              "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFOTAXIME "              "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFTAZIDIME "             "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFOXITIN "               "S"
91072 "KLEBSIELLA PNEUMONIAE"       "CEFEPIME "                "S"
91072 "KLEBSIELLA PNEUMONIAE"       "AMIKACIN "                "S"
91072 "KLEBSIELLA PNEUMONIAE"       "IMIPENEM "                "S"
91072 "KLEBSIELLA PNEUMONIAE"       "ERTAPENEM"                "S"
91072 "KLEBSIELLA PNEUMONIAE"       "MEROPENEM"                "S"
91717 "SALMONELLA PARATYPHI A"      "AMPICILLIN"               "S"
91717 "SALMONELLA PARATYPHI A"      "COTRIMOXAZOLE"            "S"
91717 "SALMONELLA PARATYPHI A"      "AMOXYCILLIN-CLAVULANATE"  "S"
91717 "SALMONELLA PARATYPHI A"      "PIPERACILLIN "            "S"
91717 "SALMONELLA PARATYPHI A"      "PIPERACILLIN/TAZOBACTAM " "R"
91717 "SALMONELLA PARATYPHI A"      "CIPROFLOXACIN "           "S"
91717 "SALMONELLA PARATYPHI A"      "LEVOFLOXACIN "            "S"
91717 "SALMONELLA PARATYPHI A"      "MOXIFLOXACIN "            "I"
91717 "SALMONELLA PARATYPHI A"      "CEFOTAXIME "              "S"
91717 "SALMONELLA PARATYPHI A"      "CEFTAZIDIME "             "S"
91717 "SALMONELLA PARATYPHI A"      "CEFEPIME "                "S"
91717 "SALMONELLA PARATYPHI A"      "IMIPENEM "                "S"
91717 "SALMONELLA PARATYPHI A"      "ERTAPENEM"                "S"
91717 "SALMONELLA PARATYPHI A"      "MEROPENEM "               "S"
91717 "SALMONELLA PARATYPHI A"      "CEFTRIAXONE"              "S"
91717 "SALMONELLA PARATYPHI A"      "NALIDIXIC ACID"           "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "ERYTHROMYCIN"             "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "CLARITHROMYCIN "          "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "CLINDAMYCIN "             "I"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "COTRIMOXAZOLE"            "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "AMOXYCILLIN-CLAVULANATE"  "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "NETILMICIN "              "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "VANCOMYCIN"               "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "DAPTOMYCIN "              "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "LINEZOLID "               "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "TETRACYCLINE "            "S"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "GENTAMICIN "              "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "CIPROFLOXACIN"            "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "CEPHALOTHIN "             "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "TEICOPLANIN "             "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "SYNERCID "                "I"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "LEVOFLOXACIN"             "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "MOXIFLOXACIN "            "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "CHLORAMPHENICOL"          "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "RIFAMPIN"                 "R"
91815 "STAPHYLOCOCCUS HAEMOLYTICUS" "OXACILLIN "               "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "ERYTHROMYCIN"             "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "CLARITHROMYCIN"           "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "CLINDAMYCIN"              "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "COTRIMOXAZOLE"            "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "AMOXYCILLIN-CLAVULANATE"  "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "NETILMICIN"               "I"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "VANCOMYCIN"               "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "LINEZOLID"                "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "TETRACYCLINE "            "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "GENTAMICIN"               "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "CIPROFLOXACIN"            "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "TEICOPLANIN"              "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "SYNERCID"                 "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "LEVOFLOXACIN"             "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "MOXIFLOXACIN"             "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "CHLORAMPHENICOL "         "S"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "RIFAMPIN"                 "R"
92423 "STAPHYLOCOCCUS HAEMOLYTICUS" "OXACILLIN"                "R"
59332 "ESCHERICHIA COLI"            "AMPICILLIN"               "R"
59332 "ESCHERICHIA COLI"            "CEFAZOLIN "               "R"
59332 "ESCHERICHIA COLI"            "GENTAMICIN"               "R"
59332 "ESCHERICHIA COLI"            "TOBRAMYCIN "              "R"
59332 "ESCHERICHIA COLI"            "COTRIMOXAZOLE"            "R"
59332 "ESCHERICHIA COLI"            "AMOXYCILLIN-CLAVULANATE"  "R"
59332 "ESCHERICHIA COLI"            "PIPERACILLIN"             "R"
end

[Pete Huckelba (StataCorp)]

Administrative bump, post was pushed to moderation.

-Pete

[Jesse Wursten]

Can you be a bit more explicit in which endresult you'd like to obtain? Right now it's not very clear.

Additionally, are you sure you want to reshape? There are very very few instances where Stata is easier to work with in a wide than in a long setting.

[Katie Tseng]

Hi Jesse,

Thank you for your response. I am merging this dataset with many similar hospital datasets that all list antimicrobial agents and their test results in a wide setting, and we'd like to work with each antimicrobial agent as it's own variable. However, I was able to resolve the problem! Apparently there were duplication observations in the dataset.

Thank you very much!

Katie