[R] Extracting data from SQL Server based on R parameters

[Jeff Newmiller]

Without write access to the SQL database, you cannot merge data from both sources within the SQL query. You can use paste to build a list of literal strings that you can refer to in the SQL where clause with IN, pull the results into R, then use the merge function to combine the data frames in R.

Build the IN clause something like

literals <- paste ("'", mMPILatestHosp$ID,"'")
inclause <- paste ("ID IN (", paste(literals, collapse =","),")", sep="")

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

dthomas <dyfed.thomas at midlandshn.health.nz> wrote:

>Hi,
>
>Can you extract data from a SQL server table based on parameters
>already in
>R? For example I have a list of unique patient IDs with latest
>hospitalisation date which I've processed in R. I need to join this
>data to
>other tables in SQL server. The tables in SQL server are too large to
>import
>without reducing the number of records. I do not have write access to
>the
>SQL server.
>
>mMPILatestHosp	#this is my  data frame of patients with unique IDs (ID)
>and
>most recent date (EVENDate)
>
>The select query is easy
>query<-"select * (plus columns in mMPILatestHosp)  from Pharm2011
>where....#This is where I want to insert mMPILatestHosp$ID in the where
>clause"
>
>I'm new to R so would appreciate your help.
>Cheers
>D
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/Extracting-data-from-SQL-Server-based-on-R-parameters-tp4319919p4319919.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.