Connect to web API using STATA and read back data in XML

[maxtop]

Hello STATA gurus,
I have medications data captured by a string variable “med1”, for example “Zocor 2010 20mg”. I need to connect to a web based database (API) that will examine the medication name and return the possible standardized medication code to my database. There is a list of commands that the web-based server recognizes and returns the requested values.
For example “approximateTerm” command for the medication presented in the example above (Zocor 2010 20mg) returns the following medication information in XML http://rxnav.nlm.nih.gov/REST/approx...g&maxEntries=3
(returns)
<rxnormdata>
<approximateGroup>
<inputTerm>zocor 10 mg</inputTerm>
<maxEntries>3</maxEntries>
<comment/>
<candidate>
<rxcui>563653</rxcui>
<score>75</score>
<rank>1</rank>
</candidate>
Etc.
I need information in the new columns: “rxcui” (it will be “563653”) and “rank” (“1”). How do I query the web based API and read this info into STATA? Any suggestions are appreciated!!!
Thanks!
Max

[Joe Canner]

Does your web API output JSON as well as XML? If so, you can use insheetjson (from SSC). I don't know if there is an XML equivalent, but people always seem to use JSON for things like this (e.g., using the Google or Mapquest APIs to geocode address data).

[maxtop]

Thanks Joe! This is very helpful- the website does output the JSON format. My question now is how to use STATA to automatically query the website and get the data back... Would appreciate any insights!

[Joe Canner]

Here is an example I did recently using the MapQuest API. It illustrates how to generate URLs for web queries and how to process the results:

capture gen str244 lat=""
capture gen str244 lng=""

forvalues i=1/`=_N' {

  local address = street[`i']+","+city[`i']+","+state[`i']+" "+zip[`i']
  local address = subinstr("`address'"," ","%20",.)

  noisily di as text "MapQuest Geocoding `i' of `=_N'"

  local url http://www.mapquestapi.com/geocoding/v1/address?&key=Fmjtd%7Cluur2qurn1%2Caa%3Do5-9aal94&inFormat=json&json={%22location%22:%22`address'%22}

  //insheetjson using "`url'", showresponse   // This is good for initial debugging, to see what is coming back

  local obs=`i'-1
  qui insheetjson lat lng using "`url'", table("results") col("locations:1:latLng:lat" "locations:1:latLng:lng") offset(`obs') replace

}

insheetjson actually takes care of both the query and the results; all of the rest is setup.

Hope this is helpful with your situation.