I'm making my owner sas data viewer in a custom task(C#), and the format need to be applied.
I refered to "SAS® 9.4 Providers for OLE DB Cookbook" and "Sample 26145: Visual Studio 2005 Visual Basic Code Snippets".
Here is my code.
var adoConnection = new ADODB.Connection();
var adoRecordset = new ADODB.Recordset();
var adoCommand = new ADODB.Command();
var server = new SasServer(Consumer.AssignedServer);
var connectString = "provider=SAS.IOMProvider.1; SAS Workspace ID={0}";
adoConnection.Open(string.Format(connectString, server.GetWorkspaceIdentifier()), "", "", 0);
adoCommand.ActiveConnection = adoConnection;
adoCommand.CommandText = "select * from sashelp.class";
adoCommand.CommandType = ADODB.CommandTypeEnum.adCmdText;
adoCommand.Properties["SAS Formats"].Value = "_ALL_";
//adoRecordset.Properties["SAS Formats"].Value = "_ALL_";
adoRecordset.Open(adoCommand, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);
//Fill an ADO.NET DataSet using the ADODB Recordset
var adapter = new OleDbDataAdapter();
var dataTable = new DataTable();
var count = adapter.Fill(dataTable, adoRecordset);
sasDataGridView1.DataSource = dataTable.DefaultView;
adoRecordset.Close();
Marshal.ReleaseComObject(adoRecordset);
adoConnection.Close();
Marshal.ReleaseComObject(adoConnection);
But it doesn't work. The DataGridView shows nothing. If I change sashelp.class to sashelp.cars, I got a "At least one of the IDs values (element 2 which is -1) is invalid." exception.
Am I missing something?
Shen
I included a simple data set viewer in this example:
Build your own SAS client app with Microsoft .NET
Here's a screenshot:
If building a custom task, you might also look at this SasDataExample. It's described in Object-oriented access to SAS data in a custom task.
Chris
Thanks, Reeza.
I haven't tried the power shell version, though I do refered it and it helped me on implementing in c#, because I want to find a solution on c# developing.
(I know that Chris posted a similar blog at Build your own SAS data set viewer using PowerShell)
Anyway, I will try the power shell sample.
I included a simple data set viewer in this example:
Build your own SAS client app with Microsoft .NET
Here's a screenshot:
If building a custom task, you might also look at this SasDataExample. It's described in Object-oriented access to SAS data in a custom task.
Chris
Thanks, Chris.
I have learned the SasDataExample custom task sample before, but missed the "Apply Format" textbox.
But this approach uses SAS.Tasks.Toolkit.Helpers.TaskDataHelpers.GetDistinctValues to get formatted values of a *single* column.
I compared the working powershell version with my .NET program on ADODB usage and found that "SAS Formats=_ALL_" recordset property only works when ADODB.CommandTypeEnum.adCmdTableDirect is set, that means if I want to filter the table, I have to create a filtered data in work library and then fetch data from that work table. Am I right?
Shen
You might be correct about that, when using the OLE DB method.
If you have a handle to the SAS Workspace, you can use the Utilities interface to get a FormatService handle, and use the FormatService to apply formatting to values for you. I'll warn you though: the methods are a little onerous to navigate. You'll find them documented in the sas.chm file within your Integration Technologies client installation directory.
Hi, Chris,
I'll read sas.chm and try the FormatService later.
For now, the SAS Formats=_ALL_ is good for me.
Really appreciate your help.
Shen.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.