Re: [R] Extracting data from SQL Server based on R parameters
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 NewmillerThe . . Go Live...
DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/BatteriesO.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---
Sent from my phone. Please excuse my brevity.
dthomas dyfed.tho...@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@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.
__
R-help@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.
[R] Extracting data from SQL Server based on R parameters
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@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.
Re: [R] Extracting Data from SQL Server
This is OT here. However, you might want to investigate the UNIQUE keyword in the SQL Server documentation for SELECT. --- Jeff NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. dthomas dyfed.tho...@midlandshn.health.nz wrote: Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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. __ R-help@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.
Re: [R] Extracting Data from SQL Server
try: SELECT a.UNIQUE_ID, a.diag01 from LoadPUS a left join CVD_ICD10 b on a.diag01 = b.[ICD-10 Codes] or a.diag02 = b.[ICD-10 Codes] or a.diag03 = b.[ICD-10 Codes] I am not sure why your table name CVD_ICD10 has a suffix $. From: Jeff Newmiller jdnew...@dcn.davis.ca.us To: dthomas dyfed.tho...@midlandshn.health.nz; r-help@r-project.org Sent: Tuesday, 10 January 2012, 8:00 Subject: Re: [R] Extracting Data from SQL Server This is OT here. However, you might want to investigate the UNIQUE keyword in the SQL Server documentation for SELECT. --- Jeff Newmiller The . . Go Live... DCN:jdnew...@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.tho...@midlandshn.health.nz wrote: Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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. __ R-help@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. [[alternative HTML version deleted]] __ R-help@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.
Re: [R] Extracting Data from SQL Server
Thanks for you help guys, your suggestion Ajay worked fine, cheers Dyfed Thomas Population Health Analyst DDI: 07 858 5967 Mob: 021 409 800 Midlands Health Network WEL House 711 Victoria Street PO Box 983 Hamilton 3240 Phone: 07 839 2888 Fax: 07 834 9242 From: Ajay Askoolum [via R] [mailto:ml-node+s789695n4281558...@n4.nabble.com] Sent: Tuesday, 10 January 2012 10:40 p.m. To: Dyfed Thomas Subject: Re: Extracting Data from SQL Server try: SELECT a.UNIQUE_ID, a.diag01 from LoadPUS a left join CVD_ICD10 b on a.diag01 = b.[ICD-10 Codes] or a.diag02 = b.[ICD-10 Codes] or a.diag03 = b.[ICD-10 Codes] I am not sure why your table name CVD_ICD10 has a suffix $. From: Jeff Newmiller [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=0 To: dthomas [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=1; [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=2 Sent: Tuesday, 10 January 2012, 8:00 Subject: Re: [R] Extracting Data from SQL Server This is OT here. However, you might want to investigate the UNIQUE keyword in the SQL Server documentation for SELECT. --- Jeff NewmillerThe . . Go Live... DCN:[hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=3 Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. dthomas [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=4 wrote: Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=5 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. __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=6 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. [[alternative HTML version deleted]] __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=7 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. If you reply to this email, your message will be added to the discussion below: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281558.html To unsubscribe from Extracting Data from SQL Server, click herehttp://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=4281000code=ZHlmZWQudGhvbWFzQG1pZGxhbmRzaG4uaGVhbHRoLm56fDQyODEwMDB8NjI5Mzc3NzM2. NAMLhttp://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespacebreadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml # Please Read the following: This message is for the named person's use only
[R] Extracting Data from SQL Server
Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ R-help@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.
