Dear All,

I am stuck trying to loop through every observation from one dataset and map the results across to another dataset. I'm really hoping someone can guide me through this:

I have 2 datasets. One contains the search terms for the various medications and their classification categories (dataset A) and the other contains the inpatient medications ordered by the doctors for each patient (dataset B). What I need to do is to map the medicine classification to the medications ordered for the patients and generate a new variables that capture the intermediateclassification categories (Final dataset)

Dataset A


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str46 searchterm1 str13 searchterm2 str7 searchterm3 str25 recodeas str27 intermediateclassification
"Metformin"                   "Glucophage"    "" "Metformin non-XR" "Metformin"    
"Metformin XR"                "Glucophage XR" "" "Metformin XR"     "Metformin"    
"Pioglitazone"                "Actos"         "" "Pioglitazone"     "TZD"          
"Rosiglitazone"               "Avandia"       "" "Rosiglitazone"    "TZD"          
"Rosiglitazone AND metformin" "Avandamet"     "" "Avandamet"        "TZD-metformin"
"MICU Intensive IV insulin"   ""              "" "MICU IVSI scale"  "Sliding scale"
"MICU subcutaneous insulin"   ""              "" "MICU SCSI scale"  "Sliding scale"
end
Dataset B

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long ïCaseNo str229 OrderMeditation
1514762004 "Iron Polymaltose (Elemental Iron 50mg/mL)  Drops"                                   
1514762004 "Lynae Vitamin D [Colecalciferol 1,000 unit, Calcium 149mg]  Tablet"                 
1514762004 "MICU Subcutaneous Insulin - Titrate per protocol Insulin Soluble 1,000unit/10mL Inj"
1514762004 "Metformin HCl  Tablet"                                                              
1514762004 "Metformin HCl XR 500mg Tablet"                                                      
1514762004 "Myotein [Protein Supplement]  Powder"                                               
end

Final dataset

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long ïCaseNo str229 OrderMeditation float(metformin slidingscale)
1514762004 "Iron Polymaltose (Elemental Iron 50mg/mL)  Drops"                                    0 0
1514762004 "Lynae Vitamin D [Colecalciferol 1,000 unit, Calcium 149mg]  Tablet"                  0 0
1514762004 "MICU Subcutaneous Insulin - Titrate per protocol Insulin Soluble 1,000unit/10mL Inj" 0 1
1514762004 "Metformin HCl  Tablet"                                                               1 0
1514762004 "Metformin HCl XR 500mg Tablet"                                                       1 0
1514762004 "Myotein [Protein Supplement]  Powder"                                                0 0
end

I know how to manually go through the list of the medications and their search term(s) using the following codes:

Code:
gen metformin = strpos(lower(OrderMeditation), "metformin") > 0
gen slidingscale = strpos(lower(OrderMeditation), "micu subcutaneous insulin") > 0

end

However, that will take really long since I have a total 104 observations in Dataset A to go through. Can someone help me please? Thanks.


Warmest regards,
Maudrene