Charles Burchill
2015-Sep-18 19:00 UTC
[R] Connecting R to SAS SPDS through ODBC - no results.
We have recently setup our SAS SPDS data server for use with ODBC connections. Unfortunately when sending a query to the data server I am getting no data returned. It is not clear if this is an issue with SPDS or RODBC and I am hoping someone has explored this already. I know the connection works since I can query data through MS Access/Excel, or STATA and have data returned. odbcConnect(ch) - works fine. odbcGetInfo(ch) - returns the server information sqlTables() returns no table information sqlQuery() returns the file structure but no records. sqlFetch() doesn't find the table (although an sqlQuery obviously does since it returns structure information) I am hoping that someone can send me an example of accessing SPDS through ODBC using R. Thanks! -- Charles Burchill, Associate Director Data Access and Use, Manitoba Centre for Health Policy College of Medicine, Faculty of Health Sciences 408-727 McDermot Ave, winnipeg, Manitoba, R3E 3P5 Tel: 204 789 3429 Fax: 204 789 3910 Charles.Burchill at umanitoba.ca<mailto:Charles.Burchill at med.umanitoba.ca> [[alternative HTML version deleted]]
Daniel Nordlund
2015-Sep-18 19:42 UTC
[R] Connecting R to SAS SPDS through ODBC - no results.
On 9/18/2015 12:00 PM, Charles Burchill wrote:> We have recently setup our SAS SPDS data server for use with ODBC connections. > > Unfortunately when sending a query to the data server I am getting no data returned. It is not clear if this is an issue with SPDS or RODBC and I am hoping someone has explored this already. > > > > I know the connection works since I can query data through MS Access/Excel, or STATA and have data returned. > > > > odbcConnect(ch) - works fine. > > odbcGetInfo(ch) - returns the server information > > > > sqlTables() returns no table information > > sqlQuery() returns the file structure but no records. > > sqlFetch() doesn't find the table (although an sqlQuery obviously does since it returns structure information) > > > > I am hoping that someone can send me an example of accessing SPDS through ODBC using R. > > > > Thanks! > > > > > > -- > Charles Burchill, Associate Director Data Access and Use, > Manitoba Centre for Health Policy > College of Medicine, Faculty of Health Sciences > 408-727 McDermot Ave, winnipeg, Manitoba, R3E 3P5 > Tel: 204 789 3429 Fax: 204 789 3910 > Charles.Burchill at umanitoba.ca<mailto:Charles.Burchill at med.umanitoba.ca> > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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. >I haven't worked with SPDS data sources, but I found that when connecting to "regular" SAS datasets I had to set believeNRows and colQuote to get the connection to work properly. For example I set up a tmp DSN and connected like this ch <- odbcConnect('tmp', believeNRows=FALSE, colQuote=NULL) Hope this is helpful, Dan -- Daniel Nordlund Bothell, WA USA