BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
nlivni
Calcite | Level 5

Hello, I can installed SAS on my local windows machine (server 2019) and am trying to parse a sas7bdat file though c# using the following commands:

 

var connection = new OleDbConnection($"Provider=sas.LocalProvider; Data Source={folder}");
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = filename;
var reader = cmd.ExecuteReader();

This produces the following error:

System.Data.OleDb.OleDbException (0x80010105): 'sas.LocalProvider' failed with no error message available, result
code: -2147417851(0x80010105).
   at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteTableDirect(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at Program.<Main>$(String[] args) in C:\ImageBuild\InstallSas\Program.cs:line 56

I have tried everything I can think of and nothing really works.  Does anyone have a direction on how to fix this?

1 ACCEPTED SOLUTION

Accepted Solutions
AlanC
Barite | Level 11

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

 

 

https://github.com/savian-net

View solution in original post

19 REPLIES 19
JosvanderVelden
SAS Super FREQ
Which sas version have you installed? Ex. SAS 9.4M7 or SAS 9.3.
Do you have excel on a pc that can access the 'server'?
If yes can you open the table in excel using an oledb connection?

Best regards, Jos
nlivni
Calcite | Level 5

Thanks,

 

It is SAS 9.4M7.  I think I know what the problem is.  I am installing sas in -quiet mode in a windows container.  Since a windows container does not have a UI, it seems to not install some of the dependencies (eg. SASUniversalViewer) even though the setup.exe reports success.  My guess is that the oledb provider can not run without this.

 

I also tried installing Sas on a Linux container and attempted using jdbc but all the documentation for jdbc seems to assume that there is a sas server somewhere and I can not figure out how to parse the files without a SAS/SHARE server.

 

Any ideas?

AlanC
Barite | Level 11
The UniversalViewer is 100% not required for OleDb. As part of the UV install, it installs OleDb. Again, go to the SAS downloads, on sas.com, and install the OleDb driver. JDBC requires the engine which you may or not have. SHARE should not be required. You are reading local. You can also use ODBC but OleDb will work.

I never install UV. Just the OleDB driver. It is here: https://support.sas.com/downloads/browse.htm?fil=&amp;cat=64
https://github.com/savian-net
nlivni
Calcite | Level 5

Have you seen an example of using the jdbc driver without a url?

 

All the examples look like this:

 

public static void main(String argv[])
 {
 Connection connection;
 int i;
 Statement statement;
 String queryString = "SELECT sup_id, sup_name " +
 "FROM mySASLib.suppliers ORDER BY sup_name";
 ResultSet result;
 double id;
 String name;
 try{
 //CONNECT TO THE SERVER BY USING A JDBC URL
 Class.forName("com.sas.rio.MVADriver");
 String user = "jdoe";
 String password = "4ht8d";
 connection = DriverManager.getConnection(
 "jdbc:sasiom://c123.na.abc.com:5671?" +
 "librefs=MySasLib 'c:\\sasdata'",
 user, password);
 //ACCESS DATA
 statement = connection.createStatement();
 result = statement.executeQuery(queryString);
 while (result.next()) {
 id = result.getDouble(1);
 name = result.getString(2);
 System.out.println(id + " " + name);
 }
 statement.close();
 connection.close();
 }
 catch(Exception e){
 System.out.println("error " + e);
 }
 }

And the connection string always looks like this: "jdbc:sasiom://c123.na.abc.com:5671?" + "librefs=MySasLib 'c:\\sasdata'"

 

The driver always complains that the url is invalid.  lookback url 127.0.0.1 also does not work.

AlanC
Barite | Level 11
I have not encountered anyone in the field using jdbc for SAS. It requires a separate access engine license. Use ODBC or OleDB for what you need.

Ignore jdbc with SAS. It is a rat hole that is not required.
https://github.com/savian-net
PaulHomes
Rhodochrosite | Level 12

As an aside, and an example of someone using JDBC with SAS in the field, as part of our Metacoda Plug-ins product we use JDBC to upload and download data processed in a SAS Workspace Server. No SAS/SHARE server and no special access engine license required - just SAS Integration Technologies as part of a common SAS 9 Platform installation.

 

I imagine you will continue with the OLE DB path but for reference, in case someone encounters this thread in future, there is a SAS Sample 33060: How to Create a JDBC Connection Using the Java Connection Factory at https://support.sas.com/kb/33/060.html which uses the MVAConnection approach that is similar to the approach we use, although we look up the SAS Workspace Server details from metadata instead.

nlivni
Calcite | Level 5
Our problem is that what we are trying to so is simply parse and read sas7bdat files in a docker container. we went down 2 paths and both seem to be a dead end. in the Linux container path all we have is jdbc and can not figure out how to use it to read files from the viya workstation install we have in the container.

in a windows container, oledb simply does not work and our SAS support told us this kind of install is not supported (although I am not sure why).
AlanC
Barite | Level 11
Saying that OleDB doesnt work does not provide enough info to help assess it. What error are you seeing?

Also, you have ODBC as another option. ODBC is more stable than any of the options and is more widely supported.

SAS tech support will tell you what they do out of the box. SAS can do a lot more than listed with creativity.
https://github.com/savian-net
nlivni
Calcite | Level 5
The oledb error is the one in this post.

odbc is an option but is it supported in the viya Linux install? If so I didn't see it there in the component list in the install.
ChrisHemedinger
Community Manager

SAS does not have a standalone sas7bdat provider that runs natively in Linux. You can use SASPy (Python library), but that must connect to a SAS session -- so you need SAS available somewhere. If you need an all-Linux solution, then you must install SAS on your Linux container. SAS does have an offering: SAS Analytics Pro for SAS Viya -- it is designed to run in a Docker container, and it installs in just a few minutes. But you would need to get a license for that.

 

Others might point out that there are 3rd party/open source libraries that purport to read SAS data sets. They are incomplete (and not supported by SAS).

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
nlivni
Calcite | Level 5

Thanks!  A few questions:

 

1. Is SASPy officially supported by SAS?

2. With SAS Analystics Pro for SAS Viya, do we have access to ODBC or are we still stuck with JDBC?  And if JDBC, do we still need a SAS server to connect to?

 

What I am getting at, is there a basic walkthrough that shows how to do this take of opening a sas7bdat file on a Linux container?

ChrisHemedinger
Community Manager

SASPy is well supported on GitHub by contributors from SAS and elsewhere.

 

With SAS Analytics Pro on SAS Viya, you have full SAS and don't need ODBC to read SAS data sets. However, if you are trying to build a connector app to a Linux container that reads data sets, then with SAS Analytics Pro on SAS Viya I think the only option you have is SASPy (which can run from a client environment and connect to your container via ssh).

 

But I have to say licensing SAS APro on SAS Viya just to read SAS data set files seems like overkill. The limiting constraint you seem to have here is "must be in Linux", which rules out the OLE DB provider that is supported on Windows.

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
nlivni
Calcite | Level 5

So if I understand correctly, the only way to read a sas7bdat file in a Linux container is to:

 

a. Install SAS Analytics Pro on SAS Viya

b. Use SASPy to read the dataset. (I think that is what this issue is saying: https://github.com/sassoftware/saspy/issues/249)

 

Am I correct?

 

PS.  Looking at the SASPy source code, it seems like it just wraps the JDBC provider and calls java directly?  Am I right?

ChrisHemedinger
Community Manager

SAS 9.4 also runs on Linux, but isn't as "deployable" as in a container as the SAS Analytics Pro on SAS Viya option I mentioned.

 

How SASPy reads data sets -- there may be a couple of different paths, but essentially it allows Pandas integration with the data in Python.

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 19 replies
  • 3504 views
  • 2 likes
  • 5 in conversation