BookmarkSubscribeRSS Feed
mmsiva
Calcite | Level 5

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>

 

 

 

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

cynthia
SASKiwi
PROC Star

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
Community Manager

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

 

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 3442 views
  • 0 likes
  • 4 in conversation