topic Re: Problem parsing sas7bdat file using the oledb provider in Developers

Problem parsing sas7bdat file using the oledb provider

nlivni — Sun, 15 Jan 2023 07:49:36 GMT

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?

Re: Problem parsing sas7bdat file using the oledb provider

JosvanderVelden — Fri, 20 Jan 2023 11:04:45 GMT

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

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Sat, 21 Jan 2023 07:09:02 GMT

It looks like you may not have installed the OleDb provider properly. Below is C# code that will check it for you. I would download it from SAS and try again. Here is some C# code I use that uses the OleDb provider. You can also see C#/SAS code on my github (savian-net (ALAN CHURCHILL) · GitHub). There may be more examples there for SAS OleDb:

        internal static List<string> GetOleDbProviders()
        {
            var oleEnum = new OleDbEnumerator();
            var elems = oleEnum.GetElements();
            var providers = new List<string>();
            if (elems != null && elems.Rows != null)
            {
                foreach (DataRow row in elems.Rows)
                {
                    if (!row.IsNull("SOURCES_NAME") && row["SOURCES_NAME"] is string)
                    {
                        providers.Add(row["SOURCES_NAME"].ToString());
                    }
                }
            }
            return providers;
        }

        public SasDataSetUtilities()
        {
            _isSasOleDbInstalled = IsSasOleDbInstalled();
        }

        public bool IsSasOleDbInstalled()
        {
            var oleDbProviders = DbUtilities.GetOleDbProviders();
            if (oleDbProviders.Any(p => p.ToUpper().StartsWith("SAS LOCAL")))
                return true;
            return false;
        }

        public DataTable GetSasDataSetMetadataAsTableUsingOleDb(string sasLibrary, string dataset)
        {
            if (_isSasOleDbInstalled is false)
            {
                Log.Error("SAS OleDb driver is not installed. Please install the driver from the SAS Support website");
                return null;
            }

            OleDbConnection sas = null;
            try
            {
                sas = new OleDbConnection(@"Provider=sas.LocalProvider; Data Source=" + sasLibrary);
                sas.Open();
                OleDbCommand sasCommand = sas.CreateCommand();
                sasCommand.CommandType = CommandType.TableDirect;
                sasCommand.CommandText = dataset;
                DataTable dt = sas.GetSchema("Columns");
                DataView dv = dt.DefaultView;
                dv.RowFilter = "TABLE_NAME = '" + dataset.ToUpper() + "'";
                sas.Close();
                return dv.ToTable();
            }
            catch (Exception ex)
            {
                Log.Error("Failed to get SAS metadata", ex);
                return null;
            }
        }

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Sun, 22 Jan 2023 11:14:06 GMT

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?

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Sun, 22 Jan 2023 12:08:14 GMT

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

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Sun, 22 Jan 2023 12:39:13 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Sun, 22 Jan 2023 14:19:12 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

ChrisHemedinger — Sun, 22 Jan 2023 14:53:40 GMT

@AlanC is correct about the SAS OLE DB provider. Also make sure you are running it with the correct bit architecture for your app. (A 64-bit app needs the 64-bit provider, etc.)

For simple debugging and "are you there" tests, try PowerShell -- example script here.

Re: Problem parsing sas7bdat file using the oledb provider

PaulHomes — Mon, 23 Jan 2023 01:31:10 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Mon, 23 Jan 2023 05:22:17 GMT

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).

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Mon, 23 Jan 2023 10:09:33 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Mon, 23 Jan 2023 11:52:44 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

ChrisHemedinger — Mon, 23 Jan 2023 12:48:22 GMT

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).

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Mon, 23 Jan 2023 13:02:42 GMT

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?

Re: Problem parsing sas7bdat file using the oledb provider

ChrisHemedinger — Mon, 23 Jan 2023 14:26:06 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Mon, 23 Jan 2023 15:27:52 GMT

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?

Re: Problem parsing sas7bdat file using the oledb provider

ChrisHemedinger — Mon, 23 Jan 2023 15:45:25 GMT

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.

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Mon, 23 Jan 2023 15:50:21 GMT

There is a python library that reads sas7bdat. Just use that. It parses the binary and doesnt rely on any db tech.

Re: Problem parsing sas7bdat file using the oledb provider

nlivni — Tue, 24 Jan 2023 05:04:06 GMT

I believe that that library is not officially supported. am I right?

Re: Problem parsing sas7bdat file using the oledb provider

AlanC — Tue, 24 Jan 2023 05:16:27 GMT

Correct. The sas7bdat is a closely held secret by SAS. I asked for it to be released 20 years ago, and made some progress, but the idea was nixed. The only way to understand it is by deciphering the binary layout. The Python folks have a lot of it figured out but SAS is not going to help.

Your call as to whether you want to go down that route.

OleDB is the best option, ODBC is second, IMO. OleDB provides more metadata. Some things you can't get via ODBC but it depends on what you need.