SAS Formats when using OleDbConnection

[AlanC]

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?

[ChrisHemedinger · Accepted solution]

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.

[AlanC]

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