Read/Write SAS dataset using c# .net and SAS universal viewer & OLEDB provider

Reply
Learner
Posts: 1

Read/Write SAS dataset using c# .net and SAS universal viewer & OLEDB provider

We have the following requirement where user is having only OLE DB and SAS universal viewer. Could you please confirm, shall we Read/Write SAS dataset using C# with local SAS universal viewer and OLEDB connection.

 

Requirement:

 

  1. Read the SAS file table ((.sas7bdat) content and push it to any of the database (SQL or Oracle) using C# .Net
  2. Generate the SAS dataset file (.sas7bdat) from database content (SQL or Oracle) using C# .Net

 

To achieve this, we are doing the following have to do the following steps in visual studio with following references.  However we are getting the issue while creating a local workspace which highlighted in yellow colour.

  • Step 1 – Add references to the SAS IOM and the SASWorkspaceManager Type Library to the project.
  • Step 2 – Obtain a SAS workspace manager object from the IOM.

 

           WorkspaceManager sasWM = new SASWorkspaceManager.WorkspaceManager();

            sasWS = (SAS.Workspace)sasWM.Workspaces.CreateWorkspaceByServer("LocalWS", Visibility.VisibilityProcess, null, "", "", out xmlInfo);

 

        

  • Step 3 – Create an OleDb connection object using the IOM provider.
  • Step 4 – Create and execute an ADO.NET command to set the location of the SAS datasets.
  • Step 5 – Create an ADO.NET data adapter object to retrieve the data from SAS datasets or create and execute an ADO.NET command to modify data in a SAS dataset.

Code for above steps:

 

SAS.Workspace sasWS;

string xmlInfo;

WorkspaceManager sasWM = new SASWorkspaceManager.WorkspaceManager();

sasWS = (SAS.Workspace)sasWM.Workspaces.CreateWorkspaceByServer("LocalWS", Visibility.VisibilityProcess, null, "", "", out xmlInfo); //Here i am getting the error

 

OleDbConnection conn = new OleDbConnection();

conn.ConnectionString = "Provider=sas.IOMProvider; SAS Workspace ID=" + sasWS.UniqueIdentifier;

// Set SAS dataset library path (libname)

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"libname MyLib 'c:\temp\IOM'";

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

// Retrieve SAS data into a DataSet

string sql = "select * from MyLib.DV";

OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

System.Data.DataSet ds = new System.Data.DataSet("SAS");

da.Fill(ds);

//WriteDataSet(ds.Tables[0]);

 

Error Details:

 

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in SASReader.exe

 

Additional information: <connectionAttempts>

 

<connectionAttempt>

 

                <sasserver></sasserver>

 

                <sasmachinednsname></sasmachinednsname>

 

                <sasport>0</sasport>

 

                <saslogin></saslogin>

 

                <status>0x80040154</status>

 

                <description>Class not registered

 

</description>

 

</connectionAttempt>

 

</connectionAttempts>

 

 

 

SAS Super FREQ
Posts: 8,820

Re: Read/Write SAS dataset using c# .net and SAS universal viewer & OLEDB provider

Hi, for a question like this, especially one with an error message, your best resource is SAS Tech Support.

cynthia
Super User
Posts: 3,238

Re: Read/Write SAS dataset using c# .net and SAS universal viewer & OLEDB provider

As I understand it, this isn't going to work. As the name suggests the SAS Universal Viewer is just for viewing existing SAS datasets. You can't use it to translate from or to other data types / sources:

 

http://support.sas.com/documentation/onlinedoc/univiewer/

 

You need to have a working version of SAS installed to do what you want - to read a SAS dataset and load it into a DBMS table or vice versa. The only other option I can think of would be a third-party tool that can translate SAS datasets 

Community Manager
Posts: 2,889

Re: Read/Write SAS dataset using c# .net and SAS universal viewer & OLEDB provider

To create a local Workspace, you need a local version of SAS installed.  And method is something like this:

 

 

  SAS.Workspace _workspace = null;
// Connect using COM protocol, locally installed SAS only SASObjectManager.IObjectFactory2 obObjectFactory = new SASObjectManager.ObjectFactoryMulti2(); SASObjectManager.ServerDef obServer = new SASObjectManager.ServerDef(); obServer.MachineDNSName = "localhost"; obServer.Protocol = SASObjectManager.Protocols.ProtocolCom; obServer.Port = 0; _workspace = (SAS.Workspace)obObjectFactory.CreateObjectByServer(Name, true, obServer, null, null);

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 252 views
  • 0 likes
  • 4 in conversation