Problem parsing sas7bdat file using the oledb provider

[nlivni]

Hello, I can installed SAS on my local windows machine (server 2019) and am trying to parse a sas7bdat file though c# using the following commands:

var connection = new OleDbConnection($"Provider=sas.LocalProvider; Data Source={folder}");
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = filename;
var reader = cmd.ExecuteReader();

This produces the following error:

System.Data.OleDb.OleDbException (0x80010105): 'sas.LocalProvider' failed with no error message available, result
code: -2147417851(0x80010105).
   at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteTableDirect(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at Program.<Main>$(String[] args) in C:\ImageBuild\InstallSas\Program.cs:line 56

I have tried everything I can think of and nothing really works.  Does anyone have a direction on how to fix this?

[JosvanderVelden]

Which sas version have you installed? Ex. SAS 9.4M7 or SAS 9.3.
Do you have excel on a pc that can access the 'server'?
If yes can you open the table in excel using an oledb connection?

Best regards, Jos

[nlivni]

Thanks,

It is SAS 9.4M7.  I think I know what the problem is.  I am installing sas in -quiet mode in a windows container.  Since a windows container does not have a UI, it seems to not install some of the dependencies (eg. SASUniversalViewer) even though the setup.exe reports success.  My guess is that the oledb provider can not run without this.

I also tried installing Sas on a Linux container and attempted using jdbc but all the documentation for jdbc seems to assume that there is a sas server somewhere and I can not figure out how to parse the files without a SAS/SHARE server.

Any ideas?

[AlanC]

The UniversalViewer is 100% not required for OleDb. As part of the UV install, it installs OleDb. Again, go to the SAS downloads, on sas.com, and install the OleDb driver. JDBC requires the engine which you may or not have. SHARE should not be required. You are reading local. You can also use ODBC but OleDb will work.

I never install UV. Just the OleDB driver. It is here: https://support.sas.com/downloads/browse.htm?fil=&cat=64

[nlivni]

Have you seen an example of using the jdbc driver without a url?

All the examples look like this:

public static void main(String argv[])
 {
 Connection connection;
 int i;
 Statement statement;
 String queryString = "SELECT sup_id, sup_name " +
 "FROM mySASLib.suppliers ORDER BY sup_name";
 ResultSet result;
 double id;
 String name;
 try{
 //CONNECT TO THE SERVER BY USING A JDBC URL
 Class.forName("com.sas.rio.MVADriver");
 String user = "jdoe";
 String password = "4ht8d";
 connection = DriverManager.getConnection(
 "jdbc:sasiom://c123.na.abc.com:5671?" +
 "librefs=MySasLib 'c:\\sasdata'",
 user, password);
 //ACCESS DATA
 statement = connection.createStatement();
 result = statement.executeQuery(queryString);
 while (result.next()) {
 id = result.getDouble(1);
 name = result.getString(2);
 System.out.println(id + " " + name);
 }
 statement.close();
 connection.close();
 }
 catch(Exception e){
 System.out.println("error " + e);
 }
 }

And the connection string always looks like this: "jdbc:sasiom://c123.na.abc.com:5671?" + "librefs=MySasLib 'c:\\sasdata'"

The driver always complains that the url is invalid.  lookback url 127.0.0.1 also does not work.

[AlanC]

I have not encountered anyone in the field using jdbc for SAS. It requires a separate access engine license. Use ODBC or OleDB for what you need.

Ignore jdbc with SAS. It is a rat hole that is not required.

[PaulHomes]

As an aside, and an example of someone using JDBC with SAS in the field, as part of our Metacoda Plug-ins product we use JDBC to upload and download data processed in a SAS Workspace Server. No SAS/SHARE server and no special access engine license required - just SAS Integration Technologies as part of a common SAS 9 Platform installation.

I imagine you will continue with the OLE DB path but for reference, in case someone encounters this thread in future, there is a SAS Sample 33060: How to Create a JDBC Connection Using the Java Connection Factory at https://support.sas.com/kb/33/060.html which uses the MVAConnection approach that is similar to the approach we use, although we look up the SAS Workspace Server details from metadata instead.

[nlivni]

Our problem is that what we are trying to so is simply parse and read sas7bdat files in a docker container. we went down 2 paths and both seem to be a dead end. in the Linux container path all we have is jdbc and can not figure out how to use it to read files from the viya workstation install we have in the container.

in a windows container, oledb simply does not work and our SAS support told us this kind of install is not supported (although I am not sure why).

[AlanC]

Saying that OleDB doesnt work does not provide enough info to help assess it. What error are you seeing?

Also, you have ODBC as another option. ODBC is more stable than any of the options and is more widely supported.

SAS tech support will tell you what they do out of the box. SAS can do a lot more than listed with creativity.

[nlivni]

The oledb error is the one in this post.

odbc is an option but is it supported in the viya Linux install? If so I didn't see it there in the component list in the install.

[ChrisHemedinger]

SAS does not have a standalone sas7bdat provider that runs natively in Linux. You can use SASPy (Python library), but that must connect to a SAS session -- so you need SAS available somewhere. If you need an all-Linux solution, then you must install SAS on your Linux container. SAS does have an offering: SAS Analytics Pro for SAS Viya -- it is designed to run in a Docker container, and it installs in just a few minutes. But you would need to get a license for that.

Others might point out that there are 3rd party/open source libraries that purport to read SAS data sets. They are incomplete (and not supported by SAS).

[nlivni]

Thanks!  A few questions:

1. Is SASPy officially supported by SAS?

2. With SAS Analystics Pro for SAS Viya, do we have access to ODBC or are we still stuck with JDBC?  And if JDBC, do we still need a SAS server to connect to?

What I am getting at, is there a basic walkthrough that shows how to do this take of opening a sas7bdat file on a Linux container?

[ChrisHemedinger]

SASPy is well supported on GitHub by contributors from SAS and elsewhere.

With SAS Analytics Pro on SAS Viya, you have full SAS and don't need ODBC to read SAS data sets. However, if you are trying to build a connector app to a Linux container that reads data sets, then with SAS Analytics Pro on SAS Viya I think the only option you have is SASPy (which can run from a client environment and connect to your container via ssh).

But I have to say licensing SAS APro on SAS Viya just to read SAS data set files seems like overkill. The limiting constraint you seem to have here is "must be in Linux", which rules out the OLE DB provider that is supported on Windows.

[nlivni]

So if I understand correctly, the only way to read a sas7bdat file in a Linux container is to:

a. Install SAS Analytics Pro on SAS Viya

b. Use SASPy to read the dataset. (I think that is what this issue is saying: https://github.com/sassoftware/saspy/issues/249)

Am I correct?

PS.  Looking at the SASPy source code, it seems like it just wraps the JDBC provider and calls java directly?  Am I right?

[ChrisHemedinger]

SAS 9.4 also runs on Linux, but isn't as "deployable" as in a container as the SAS Analytics Pro on SAS Viya option I mentioned.

How SASPy reads data sets -- there may be a couple of different paths, but essentially it allows Pandas integration with the data in Python.