I have the following connection string:
sas = new OleDbConnection($@"Provider=sas.LocalProvider.1; Data Source={_options.SasLibrary};");
I then do the normal request of the table:
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = sasDataSet;
var sasRead = sasCommand.ExecuteReader();
var dt = new DataTable();
dt.Load(sasRead);
sas.Close();
How do I specify that the data should return formatted? We used to have "SAS Formats" = _ALL_ but that does not work (I have tried numerous variations). How can we pass the SAS Formats property to a .NET Core OleDb provider?
In PowerShell, I've used this to set the Properties on a connection/recordset.
try { $objConnection.Open("Provider=SAS.LocalProvider;Data Source=`"$filePath`";") $objRecordset.ActiveConnection = $objConnection $objRecordset.Properties.Item("SAS Formats").Value = "_ALL_" # open the data set # IMPORTANT: passing in a "missing" value for the connection # because the connection is already on the RecordSet object $objRecordset.Open($filename, [Type]::Missing, $adOpenDynamic, ` $adLockOptimistic, ` $adCmdTableDirect) $objRecordset.MoveFirst() # read all of the records within the SAS data file do { # build up a new object with the field values $objectRecord = New-Object psobject for ($i = 0; $i -lt $objRecordset.Fields.Count; $i++) { # add static properties for each record $objectRecord | add-member noteproperty ` -name $objRecordset.Fields.Item($i).Name ` -value $objRecordset.Fields.Item($i).Value; } # emit the object as output from this script $objectRecord # move on to the next record $objRecordset.MoveNext() } until ($objRecordset.EOF -eq $True) # close all of the connections $objRecordset.Close() $objConnection.Close() }
But I don't know how that translates to .NET Core and the approach you're using with the OleDbCommand.
In PowerShell, I've used this to set the Properties on a connection/recordset.
try { $objConnection.Open("Provider=SAS.LocalProvider;Data Source=`"$filePath`";") $objRecordset.ActiveConnection = $objConnection $objRecordset.Properties.Item("SAS Formats").Value = "_ALL_" # open the data set # IMPORTANT: passing in a "missing" value for the connection # because the connection is already on the RecordSet object $objRecordset.Open($filename, [Type]::Missing, $adOpenDynamic, ` $adLockOptimistic, ` $adCmdTableDirect) $objRecordset.MoveFirst() # read all of the records within the SAS data file do { # build up a new object with the field values $objectRecord = New-Object psobject for ($i = 0; $i -lt $objRecordset.Fields.Count; $i++) { # add static properties for each record $objectRecord | add-member noteproperty ` -name $objRecordset.Fields.Item($i).Name ` -value $objRecordset.Fields.Item($i).Value; } # emit the object as output from this script $objectRecord # move on to the next record $objRecordset.MoveNext() } until ($objRecordset.EOF -eq $True) # close all of the connections $objRecordset.Close() $objConnection.Close() }
But I don't know how that translates to .NET Core and the approach you're using with the OleDbCommand.
Thanks Chris. I ultimately did something very similar. I will be pushing the whole solution to github at https://github.com/savian-net/ExportFromSasDataset
Here is the pertinent snippet for posterity:
/// <summary>
/// Gets a SAS dataset from the specified location but using the SAS formats for the results. Only supported
/// Base SAS formats will be handled. If a user-defined format is encountered, the method will fail.
/// </summary>
/// <param name="sasLibrary">The physical location of the SAS library to read in the data </param>
/// <param name="sasDataSet">The name of the SAS dataset to read</param>
/// <returns>.NET datatable. All values returned will be strings.</returns>
public static DataTable GetDataSet(string sasLibrary, string sasDataSet)
{
DataTable dt = new DataTable();
try
{
ADODB.Recordset set = new ADODB.Recordset();
ADODB.Connection conn = new ADODB.Connection();
conn.Provider = "sas.LocalProvider.1";
conn.Properties["Data Source"].Value = sasLibrary;
conn.Open();
set.ActiveConnection = conn;
var connProps = conn.Properties.OfType<ADODB.Property>().Select(p => new { p.Name, p.Value }).ToList();
var recordSetProps = set.Properties.OfType<ADODB.Property>().Select(p => new { p.Name, p.Value }).ToList();
if (_options.Formatted)
{
set.Properties["SAS Formats"].Value = "_ALL_";
}
set.Open(sasDataSet, Missing.Value, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdTableDirect);
OleDbDataAdapter da = new OleDbDataAdapter();
da.Fill(dt, set);
dt.TableName = sasDataSet.ToUpper();
conn.Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.