SAS/SHARE, SAS/Connect, reading a dataset using C#

[js8765]

Hi,

For days I am trying to connect to my data from a C# program. I have tried many different ways (Integration Technologies, ADODB, OLEDB) and many different connection strings but none have worked so far.

Here is how I would connect to the data (using SAS/SHARE and SAS/Connect) from within a SAS Session,

%let conn1 = abc123.xyz.com         9999;

signon conn1;  *< here I enter my username and password in the pop-up window>;

libname mylib remote '/dir1/dir2/dir3' server = conn1;

Has anyone any experience in how to read a dataset on a different server using C#? I'm sure it's my connection string that's incorrect.

It would be really amazing if someone could suggest some code using the details as above (server:abc123.xyz.com port:9999 UserId: username Password: password library to get dataset from:mylib) . Like I said, I have been trying for a long time and have look at all the cookbooks and articles I could find, that's why a concrete connection string or C# code would be really appreciated as opposed to a pointer to reference material.

Thanks in advance for any help!

[Unkie_SAS]

How about page 41 of http://support.sas.com/documentation/cdl/en/oledbpr/65038/PDF/default/oledbpr.pdf ?

It is dependent on you having a SAS/Share Server to connect to.

-S

[js8765]

Hi Unkie, Thanks for the pointer. I had already tried this code but get an error. "TCP method failed" "Unable to locate server". The code snippet in the link you sent doesn't accommodate a value for PORT, which I think is needed.

With regards to having a SAS/Share Server , I'm assuming that's what the code in the original post is connecting to, but I could be wrong.

[Unkie_SAS]

In your OP you establish a SAS/Connect session.

A SAS/Share Server is initiated on the 'remote' machine by executing PROC SERVER.

SAS/Connect and SAS/Share are different licensable products and you may not have the latter licensed. Use PROC SETINIT (on the server) to determine whether you do or not.

SAS/SHARE can be used to surface data to clients, if you want to execute SAS code on the server (compute logic) you would need SAS Integration Technologies licensed on the server.

[SASKiwi]

Does your connection string work OK when used inside SAS?

[js8765]

Hi SASKiwi, do you know of a way to test a connection string from within SAS? This would be great as it might give better indications as to what part of the string is wrong or missing.

I have connected to the server using the code in the original post, so the values I'm using there are definitely correct. However it's proving a nightmare to convert that information into a ConnectionString that works in C# 😞

[Ksharp]

As I remember, the only way to talk to Java,C# is Integration Technologies , Check its developing documentation firstly.

[SASKiwi]

You should be able to use this:

%let conn1 = abc123.xyz.com         9999; 

signon conn1;  *< here I enter my username and password in the pop-up window>";

in a SAS session to test if it works. You can either do this from a PC with "complete" SAS on it (not just EG) or if you don't have this you could remote login to your SAS server, start a SAS session and try it there. In this case you would be trying to connect to a second SAS session on the same server. SAS/CONNECT would have to be installed and licenced on both client and server computers for this to work. 

[AndreasMenrath]

This is the connection string Excel generates for me if i use the OLEDB Wizard:

Provider=sas.SHAREProvider.9.3;User ID=username;Password = password;Data Source=9999;Location=abc123.xyz.com;Mode=ReadWrite|Share Deny None;SAS Local Server=0;SAS Executable=C:\Program Files\SASHome\x86\SASFoundation\9.3\sas.exe;SAS Parameters=-initstmt %sasodbc(sdplserv) -icon -nologo -notutorialdlg;SAS Working Directory=C:\Program Files\SASHome\x86\SASFoundation\9.3;SAS Server Release=9

Notice that Data Source is the port of the server.

Now you should be able to use this connection to use standard SQL queries to query your data, e.g.

SELECT * FROM '/dir1/dir2/dir3/mydata.sas7bdat'

[AndreasMenrath]

Here is a powershell script which works with OleDB and the Share Server:

$conn = "Provider=sas.SHAREProvider.;User ID=username;Password=password;Data Source=9999;Location=abc123.xyz.com";

$sql = "Select * from sashelp.class";

$conn = New-Object System.Data.OleDb.OleDbConnection($Conn);

$conn.open();

$cmd = New-Object System.Data.OleDb.OleDbCommand($sql,$conn);

$ada = New-Object System.Data.OleDb.OleDbDataAdapter($cmd);

$table = New-Object System.Data.DataTable;

$ada.Fill($table) | Out-Null;

$table | Out-GridView;

$conn.Close();