Problem parsing sas7bdat file using the oledb provider

[AlanC]

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

[nlivni]

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

[AlanC]

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. 

[ChrisHemedinger]

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

[AlanC · Accepted solution]

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;
            }
        }