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.
@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.
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;
}
}
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.