topic Re: sas data viewer custom task format issue in SAS Enterprise Guide

sas data viewer custom task format issue

ShenQicheng — Thu, 21 Jan 2016 02:10:13 GMT

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

Re: sas data viewer custom task format issue

Reeza — Thu, 21 Jan 2016 03:14:33 GMT

Is this useful at all?

https://gist.github.com/FriedEgg/55ec275ae1e1c8fcbe6a

Re: sas data viewer custom task format issue

ShenQicheng — Thu, 21 Jan 2016 05:19:35 GMT

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.

Re: sas data viewer custom task format issue

ChrisHemedinger — Thu, 21 Jan 2016 13:12:04 GMT

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

Re: sas data viewer custom task format issue

ShenQicheng — Tue, 26 Jan 2016 07:45:05 GMT

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

Re: sas data viewer custom task format issue

ChrisHemedinger — Tue, 26 Jan 2016 12:55:28 GMT

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.

Re: sas data viewer custom task format issue

ShenQicheng — Wed, 27 Jan 2016 02:31:11 GMT

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.