BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AlanC
Barite | Level 11

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?

 

 

https://github.com/savian-net
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

2 REPLIES 2
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
AlanC
Barite | Level 11

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

 

https://github.com/savian-net

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 833 views
  • 2 likes
  • 2 in conversation