[R] Connecting R to SAS SPDS through ODBC - no results.

[Daniel Nordlund]

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