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

[mmsiva]

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>

[Cynthia_sas]

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

cynthia

[SASKiwi]

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 

[ChrisHemedinger]

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