topic Re: Fastest way to read SAS table in C#? in Developers

Fastest way to read SAS table in C#?

keV — Thu, 16 Jan 2020 11:31:12 GMT

I am currently reading SAS tables using DbCommand.ExecuteReader with CommandType.TableDirect.

Is this the fastest way to read data?

Thanks

Kevin

Re: Fastest way to read SAS table in C#?

Oligolas — Tue, 21 Jan 2020 12:24:18 GMT

I'll suspect XMLs to be faster if you have them. But with sas7bdat, maybe you can have a look at these papers: SESUG paper SAS Support and Blog.

Re: Fastest way to read SAS table in C#?

Ksharp — Thu, 16 Jan 2020 12:35:35 GMT

Maybe @ChrisHemedinger could give you a hand.

Re: Fastest way to read SAS table in C#?

ChrisHemedinger — Thu, 16 Jan 2020 13:00:40 GMT

It depends on the dimensions of the table and whether you're reading it as a bulk set or paging in across groups of observations.

I have a simple example in the SASHarness application in GitHub. If you don't need all the data at once, I suggest applying data set options like FIRSTOBS, OBS=, KEEP and/or DROP, and WHERE= to limit the data that SAS will return. This is assuming that you are connecting to a SAS session to read this data. The SAS data providers will read the entire row, so wider data is naturally slower to read. You can limit this if you don't need all columns by applying KEEP or DROP when you open the data.

If reading sas7bdat files directly using the SAS Local OLE DB provider (no SAS session), then options are more limited, I think. I have a Windows PowerShell example that you may be able to adapt.

Re: Fastest way to read SAS table in C#?

AhmedAl_Attar — Thu, 16 Jan 2020 13:09:50 GMT

If you don't have SAS software licensed, you may try to integrate the following command-line tool and MIT-licensed C library into your C# code

Hope this helps,

Ahmed

Re: Fastest way to read SAS table in C#?

keV — Thu, 16 Jan 2020 14:54:09 GMT

Thanks to everyone for your speedy replies. There doesn't seem any obvious alternative to what I'm already doing.

I have been using the Local provider with the ADO .Net recipe referenced in one of the replies for several years now, but one of our clients has complained of slow reading with large datasets. However, the reading from the SAS file is just one part of the overall process so I will go and do some further investigation of the whole process and see where the bottlenecks are.

Thanks again for everyone's interest..

Re: Fastest way to read SAS table in C#?

AlanC — Thu, 16 Jan 2020 15:12:59 GMT

I have a lot of C# around SAS. Here is a method to handle the read. I would defer to Chris H.'s suggestions and drop/keep as many variables as you can. Contact me directly for a dll if needed. The below is in Framework: I have not converted to Core as of yet. OleDb is in flux on core and I haven't looked at latest info.

public DataTable GetDataSet(string sasLibrary, string dataset)
{
OleDbConnection sas = null;
DataTable dt = new DataTable(dataset);
try
{
sas = new OleDbConnection(@"Provider=SAS.LocalProvider.1; Data Source=" + sasLibrary);
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = dataset;
OleDbDataReader sasRead = sasCommand.ExecuteReader();
dt.Load(sasRead);
sas.Close();
}
catch (Exception ex)
{
sas.Close();
string errMessage = "Unable to get the SAS dataset. Library: " + sasLibrary + ", DataSet: " + dataset + ", " +
ex.TargetSite.Name;
HandleError(MethodBase.GetCurrentMethod().Name, ex);
}
finally
{
sas.Close();
}
return dt;
}

public DataTable GetSasDataSetMetadata(string sasLibrary, string dataset)
{
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)
{
HandleError(MethodBase.GetCurrentMethod().Name, ex);
return null;
}
}

Re: Fastest way to read SAS table in C#?

keV — Tue, 21 Jan 2020 11:48:30 GMT

Thanks - this is exactly what I'm already doing, but exposing the DataReader rather than pre-reading into a table.

I think this is safer and less memory intensive for large tables.

Re: Fastest way to read SAS table in C#?

AlanC — Tue, 21 Jan 2020 12:54:46 GMT

What are you using for .NET? Is it Framework or Core and what version,please.

Re: Fastest way to read SAS table in C#?

keV — Tue, 21 Jan 2020 13:08:02 GMT

.Net Framework 4.6.1

Re: Fastest way to read SAS table in C#?

AlanC — Tue, 21 Jan 2020 13:25:24 GMT

If possible, switch to .NET Core 3.1.1. OleDb is supported on .NET Core 3+. Why? It is a lot faster than Framework and you will probably get an intrinsic boost. Plus, Core is the way to go for all future stuff as well.

Take a look at this SO article:https://stackoverflow.com/questions/40718537/how-to-convert-idatareader-into-stream-in-c-sharp

Specifically,

public IEnumerable<Order> GetOrders()
{
   IDbCommand cmd = ...  <<build your command here>> ...
   using(var rdr = cmd.ExecuteDataReader())
   {
      while(rdr.Read())
      {
          Order order = new Order {Id=rdr.GetDecimal(1), Name=rdr.GetString(2)};
          yield return order;
      }
   }
}

I may be able to test something but I am pretty busy right now. I think switching to Core is needed unless there is something compelling keeping you away. I have been using Core for a long time and honestly, Core 3.1 is fantastic.

Re: Fastest way to read SAS table in C#?

keV — Tue, 21 Jan 2020 13:36:38 GMT

Thanks for the tip Alan. SAS data is just one part of a data reading component, which we have already partially migrated to .Net Core. OleDb wasn't available at the time in .Net Core. We'll definitely look at that soon.

Why do you think it is faster when it is still using SAS's OleDB component?

Re: Fastest way to read SAS table in C#?

AlanC — Tue, 21 Jan 2020 13:43:53 GMT

OleDb support in Core was a hot topic for a number of shops migrating from Framework to Core due to things like Access DB. It was added in in May 2019 and was put into Core 3.0 in Sept as a release.

I cannot say for sure that it will be faster. It requires testing. Heck, it may not work but give it a try in a small, isolated test project.

You are welcome to reach out to me directly as well. If I get a chance, I will try and test on my own.

Re: Fastest way to read SAS table in C#?

keV — Tue, 21 Jan 2020 14:31:46 GMT

Yep, we've been waiting for the OleDb support. Hopefully we'll find some time to address it shortly. Thanks again for all your help. I'll keep you posted.

Re: Fastest way to read SAS table in C#?

AlanC — Wed, 22 Jan 2020 05:49:33 GMT

This works in Core 3.1.1 with nuget System.Data.OleDb and SAS Provider for OleDb 9.48. Solution attached:

        static void Main(string[] args)
        {
            Console.WriteLine("Starting test...");
            var dt = ReadSasDataset(@"z:\scratch", "shoes");
        }

        private static DataTable ReadSasDataset(string sasLibrary, string dataset)
        {
            OleDbConnection sas = null;
            DataTable dt = new DataTable(dataset);
            try
            {
                sas = new OleDbConnection(@"Provider=SAS.LocalProvider.1; Data Source=" + sasLibrary);
                sas.Open();
                OleDbCommand sasCommand = sas.CreateCommand();
                sasCommand.CommandType = CommandType.TableDirect;
                sasCommand.CommandText = dataset;
                OleDbDataReader sasRead = sasCommand.ExecuteReader();
                dt.Load(sasRead);
                sas.Close();
            }
            catch (Exception ex)
            {
                sas.Close();
                string errMessage = "Unable to get the SAS dataset. Library: " + sasLibrary + ", DataSet: " + dataset + ", " +
                    ex.TargetSite.Name;
            }
            finally
            {
                sas.Close();
            }
            return dt;
        }

Re: Fastest way to read SAS table in C#?

keV — Thu, 30 Jan 2020 13:19:17 GMT

Well, I've tried it (no code change required) with Core 3.1.1. and the reading of a large file is consistently a few % slower.

Oh well, worth a try.

Re: Fastest way to read SAS table in C#?

AlanC — Thu, 30 Jan 2020 15:04:48 GMT

You are welcome to contact me directly and we can figure out approaches. alan.churchill@savian.net

Re: Fastest way to read SAS table in C#?

yoyo02 — Thu, 12 Mar 2020 14:30:49 GMT

Hi keV
Do you mind sharing the code that allows you to read from SAS tables ?

thanks

Re: Fastest way to read SAS table in C#?

AlanC — Thu, 12 Mar 2020 17:09:04 GMT

I posted a zip file on page 1 of this thread.

Re: Fastest way to read SAS table in C#?

keV — Thu, 12 Mar 2020 17:12:00 GMT

AlanC just beat me to it. The code is also clearly shown in two of the other posts in the thread.