topic Re: Where clause generator in a stored process using SAS Prompting Manager? in Microsoft Integration with SAS

Where clause generator in a stored process using SAS Prompting Manager?

ScottBass — Mon, 29 Jul 2013 01:17:30 GMT

SAS 9.3

AMO 5.1

Summary: Can I create a robust, flexible where clause generator in a stored process using the SAS Prompting Manager?

Details: I wasn't sure whether to post this in this group or the stored process group. I decided to post here, since the solution is using AMO.

Here is my business process/design criteria:

* The end users of this "application" (really just a set of canned queries) are not "power users", nor do I want them to be. The solution must be simple and easy to use.

* The "landing zone" for the data is Excel, so I will be using AMO.

* The main purpose of the application is to select desired columns and create a filter (where clause) against large fact tables (SPDE engine with indexes).

* Once the data is queried, sometimes that is enough, so the built-in "Open SAS data source" and "Filter and Sort" functionality would work fine in this scenario.

* Other times, we need to join additional dimension tables to the query results (so will need to use stored processes/AMO "Reports") or apply more complete post-processing.

In summary, the solution needs to be: easy to use, select columns and rows, sometimes post-processing, sometimes not.

Ideally, I would like to mimic the Filter and Sort functionality using the SAS Prompting Manager, i.e.:

* User selects a source dataset

* A dynamic list of columns is available in a dropdown list based on the chosen data source

* A static list of operators is available

* A "Get Values" functionality is available for the data values

* Proper quoting/no quoting for character/numeric data

* An "And/Or" dropdown to link the statement elements together.

In summary, I don't know how to do this using the SAS Prompting Manager, or even if it's possible. (And if it is, and your wheel is perfectly round, can you share your invention with me )

As I see it, I have several design approaches:

1) Use the built-in functionality of "Add SAS Data Source" and "Filter and Sort" to subset the fact table and return the rows to Excel, perhaps in a hidden worksheet. (I sure wish there was a way to save the results of that query in a SAS dataset on the server session.) Use a separate stored process with an input stream to send that data back to SAS as an input stream (Yuck, re: performance and proper data typing). From there, I can use that to post process the filtered data as required.

2) Use the SAS Prompting Manager to create prompts for hardcoded columns, i.e. the ones most used for filtering. I could augment that with a text entry field where they could add additional, free form where criteria. Not ideal, since they aren't power users. Nor does it address the stated design criteria.

3) Try to do something with VBA and VBA form elements.

4) (It doesn't look like AMO supports custom plugins, like EG, so I can't roll my own UI)

5) (In the good old days, I'd use SAS/AF, but the end users don't have Base SAS on their machines. I sure wish the SAS Prompting Manager was more full-featured...)

6) Write my own .Net application and IOM. A bit overkill for this simple task, although it would be fun . But, I don't know how to "drive" AMO from a .Net application, and for various reasons want to use AMO.

I'm hoping there's some bright idea I'm missing here. So, in conclusion, using Excel and AMO as a starting point, is there a way to write a stored process that has a generic, full featured where clause generator based on a data source that the user has dynamically chosen at run time? Otherwise, how would you approach this from a design and architecture perspective?_

Thanks,

Scott

Re: Where clause generator in a stored process using SAS Prompting Manager?

Scott_Mitchell — Mon, 29 Jul 2013 01:52:36 GMT

I have no answer to this question, as my exposure to Stored Processes is limited.

I just wanted to say thank you for including so much information in your post, I am sure someone will have an answer for you soon.

Re: Where clause generator in a stored process using SAS Prompting Manager?

ScottBass — Mon, 29 Jul 2013 04:24:52 GMT

Thanks...and thanks too to your parents for giving you such a great name :smileygrin:

When I err, I often err on the side of too much information, rather than not enough. So, perhaps sometimes my messages won't get read, or only skimmed. But, with the time zone difference between Sydney and the US (where most of the answers usually originate), I take the chance of too much detail. Otherwise, it can be days going back and forth clarifying the problem statement. Or else I'm sending real time updates at 2AM my time...

Re: Where clause generator in a stored process using SAS Prompting Manager?

jakarman — Mon, 29 Jul 2013 06:51:05 GMT

For the first part:

* User selects a source dataset

* A dynamic list of columns is available in a dropdown list based on the chosen data source

With 9.3 it is possible te create a prompt based on a input-table.

You need to define the table and all fields of it before able to use it. That part won't be too difficult.

Afterwards you can use it in your Stored process prompt.

There is some behavior to deal with.

If you want to use a StoredProcess Server for you prompt you have to imagine that the prompt will not use that instead it is the WorkspaceServer.
Do you want to use this at several "app-server contexts" (different departments / security). And a user (power-user) has access to several of them. You can not trust wich app-server context will open. It can be quite different to the expected one. As long the content of the tabel is exactly equal you will not notice that.

Not really helpfull for a working solution or maybe it works for you.

http://analytics.ncsu.edu/sesug/2011/BB11.Droogendyk.pdf

Re: Where clause generator in a stored process using SAS Prompting Manager?

ScottBass — Mon, 29 Jul 2013 08:11:30 GMT

Hi Jaap,

Thanks. Let me restate to make sure I understand:

* create a table of metadata. For example:

proc contents data=sashelp._all_ out=work.columns (keep=libname memname name type) noprint;

run;

proc sort data=work.columns (keep=libname memname) out=work.tables nodupkey;

by libname memname;

run;

* then, use dynamic prompts plus dependencies to allow the user to select the library --> table --> columns

I just had a play with data source item prompts, multiple items (or multiple ordered items). I had not used these prompts before. They appear to meet my needs re: selecting a column, but the entire architecture is a far cry from the where clause generator I need to create.

I'd like to create the same interface as the Filter and Sort functionality, i.e. (excuse the ASCII art):

<variable name> V (dynamic dropdown list) || <operator> V (dropdown static list) || <value(s)> || [...] (get values) || <AND|OR> V (dropdown)

all on one line, combining each line to make a syntactically correct where statement. And if not that, then something just as simple for the end user.

Right now, all I can think of is hard coding the UI components to the "main" variables in each of the datasets to be filtered. But I can see it now: "End User: Can you add this variable to the query?" Pretty soon I've hard coded 50 or so UI elements to the dataset, times X number of datasets to query .

I can do all the behind-the-scenes macro gyrations if I can somehow get the Prompting Mangler to generate my desired user interface. (Which was oh so easy in "old" SAS/AF)

Regards,

Scott

Re: Where clause generator in a stored process using SAS Prompting Manager?

jakarman — Mon, 29 Jul 2013 08:41:36 GMT

To create the table is the first step.
Making it dynamic is the next one. This is requiring all the registrations within the metadata (tabels/fields). Information map studio etc.

That part can be given and is needed as the dynamic prompts.

By the way, Is te usage of information maps not giving something usuable for you?

It is meant to deal defining views on data. Limiting views on tables/colums looks something you are needing.

It looks that you are trying to rebuild the query manager as available in EGuide but has additional limiting requirements.

Compared to SAS/AF the application building has moved to adding a lot of metadata definitons. It means thinking developing aside from coding flow-diagrams and processing also with object/properties views and adding security at an other way.

Re: Where clause generator in a stored process using SAS Prompting Manager?

ScottBass — Mon, 29 Jul 2013 23:06:59 GMT

Jaap Karman wrote:

To create the table is the first step.
Making it dynamic is the next one. This is requiring all the registrations within the metadata (tabels/fields). Information map studio etc.

That part can be given and is needed as the dynamic prompts.

By the way, Is te usage of information maps not giving something usuable for you?

It is meant to deal defining views on data. Limiting views on tables/colums looks something you are needing.

It looks that you are trying to rebuild the query manager as available in EGuide but has additional limiting requirements.

We don't have a full EBI license so we don't have Information Map Studio.

Yes, I'm trying to replicate the query manager as in EG/Filter and Sort in AMO. The query needs to be dynamic, not canned as in IMS or a hard coded stored process.

I want to build a UI that will allow me to 1) select a data source, 2) select desired columns, 3) generate a where clause, 4) generate proc sql code, all within a stored process.

Re: Where clause generator in a stored process using SAS Prompting Manager?

Reeza — Tue, 30 Jul 2013 04:10:03 GMT

I can see 1/2/ being possible.

1) Create a static prompt with the list of all datasources - one macro variable

2) Create a cascading prompt with the list of variables from the selected source - one macro variable (list)

This is where I start to see some issues.

3) Create filter/where clause - Not sure what options you want in here, may want to be more specific. How will you allow the user to enter it?

4) You can use the macro variables to generate the code, except for the joins...that's where I can't quite see it...yet . I don't know what you're joining for though, perhaps formats may be a better solution somehow?

It's an interesting discussion though...

Re: Where clause generator in a stored process using SAS Prompting Manager?

Tushar — Tue, 30 Jul 2013 07:10:47 GMT

Hi Scott Bass,

As per your requirement look like you need to work with the VBA and SAS, since you have mentioned you dont have full EBI tool , so i searched and found one doc online which might help you to solve your issue at some extent,

http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

and Apology incase if it is not at all helpful for your issue.

Regards,

Tushar J.

Re: Where clause generator in a stored process using SAS Prompting Manager?

ScottBass — Tue, 30 Jul 2013 10:35:16 GMT

Hi Reeza,

Thanks for your reply...

1) I created a data source prompt, but what I get in the SAS code is the metadata path. For example:

DATASOURCE_1=/Shared Data/CLASS(Table)

DATASOURCE_1_TYPE=1

Um, I'm gonna need sashelp.class somewhere in my code. /Shared Data/CLASS(Table) is less than helpful.

2) I created a data source item prompt, multiple ordered values, allow users to select from multiple data sources. The user interface looks pretty promising. However, what I get in the SAS log is:

DATASOURCEITEM_5=Name

DATASOURCEITEM_50=5

DATASOURCEITEM_51=Name

DATASOURCEITEM_52=Sex

DATASOURCEITEM_53=Region

DATASOURCEITEM_54=Product

DATASOURCEITEM_55=Subsidiary

DATASOURCEITEM_5_COUNT=5

DATASOURCEITEM_5_PATH=/Shared Data/CLASS

DATASOURCEITEM_5_PATH1=/Shared Data/CLASS

DATASOURCEITEM_5_PATH2=/Shared Data/CLASS

DATASOURCEITEM_5_PATH3=/Shared Data/SHOES

DATASOURCEITEM_5_PATH4=/Shared Data/SHOES

DATASOURCEITEM_5_PATH5=/Shared Data/SHOES

DATASOURCEITEM_5_SOURCE_TYPE=1

DATASOURCEITEM_5_SOURCE_TYPE1=1

DATASOURCEITEM_5_SOURCE_TYPE2=1

DATASOURCEITEM_5_SOURCE_TYPE3=1

DATASOURCEITEM_5_SOURCE_TYPE4=1

DATASOURCEITEM_5_SOURCE_TYPE5=1

DATASOURCEITEM_5_TYPE=1

DATASOURCEITEM_5_TYPE1=1

DATASOURCEITEM_5_TYPE2=1

DATASOURCEITEM_5_TYPE3=1

DATASOURCEITEM_5_TYPE4=1

DATASOURCEITEM_5_TYPE5=1

Um, what I NEED is something like:

DATASOURCEITEM_51=SASHELP.CLASS.Name

DATASOURCEITEM_52=SASHELP.CLASS.Sex

DATASOURCEITEM_53=SASHELP.SHOES.Region

DATASOURCEITEM_54=SASHELP.SHOES.Product

DATASOURCEITEM_55=SASHELP.SHOES.Subsidiary

or something like that. And isn't this rather like database column syntax that's been around forever? It's not like SAS even needed to reinvent a new syntax. THIS I could parse in macro code!

For the end programmer trying to actually DO something with the results (macro variables) of the prompting mangler, /Shared Data/CLASS is pretty useless. Whoever dreamed up this syntax wasn't a SAS programmer, but I would have thought SAS would have done some usability testing before releasing this (prompting mangler) on their users.

If someone is aware of a way to translate a macro variable containing the metadata path to a table definition, to the libref and memname of the table that it references, please let me know. A pointer to the doc example would be fantastic.

So, for now I've defined in metadata tables from the SASHELP dictionary views, i.e. VSLIB, VSTABLE, VTABLE, VCOLUMN, etc, and bound them to dynamic list text prompts. VSTABLE depends on VSLIB. VCOLUMN depends on VSLIB and VSTABLE, etc.

3) I'm not sure how to make it clearer??? I want the same or similar functionality available to the end user of a stored process as that available in EG or AMO Filter & Sort task, Filter tab, i.e. a point and click, easy to use way to generate a dynamic, syntactically correct where clause based on the source dataset.

4) I'm happy to hard code the joins in the stored process. It's the where clause against the fact table that I want to be dynamic and flexible. I'll probably use a hash object join to link in dimension data rather than SQL anyway, which is easier syntax to generate in the stored process.

Finally, I have come up with something "interesting", but not sure if I'll use it or not. Performance is a major consideration, especially with a large number of rows, as well as the overall user experience.

I'll describe it here. If you have AMO, you may want to play along...

In Excel, use the SAS Data task to drop a table on Sheet1. I used SASHELP.PRDSALE.
Use the built-in Filter and Sort menu to drop columns, change their order, add a where clause, and sort the results. Click OK and view the results. Note: This will create the Named Range Table1.
I've created a stored process "Create Dataset from Excel Table". It has 5 text prompts: InLib, InData, OutLib, OutData, and Return (return the dataset to Excel? No=0, Yes=1)
InLib and InData MUST match the table selected for Sheet1, i.e. SASHELP.PRDSALE. This is needed to create the correct variable attributes of the uploaded dataset. (Hmmm...I may be able to get the dataset name from the DataView object name (SASApp:SASHELP.PRDSALE) and use VBA to set the parameter of the stored process (so perhaps make it a hidden parameter). This would be an improved user experience.)
The stored process has an input stream defined as xl_data, text/xml, rewind.
Drop this stored process on Sheet2. When you initially drop the stored process, it will ask for the input stream. Enter =Table1[#ALL].

Here is the code for the stored process:

%macro CreateDatasetFromExcelTable;

libname xl_data xmlv2;

* get variables from instream dataset ;

proc contents data=xl_data.&_webin_sasname out=work.contents (keep=name varnum) noprint;

run;

proc sql noprint;

select name into :vars separated by " "

from work.contents

order by varnum

;

quit;

data &outlib..&outdata;

format &vars; %* set PDV order based on Excel table ;

if 0 then set &inlib..&indata (keep=&vars); %* set variable attrs based on source table ;

set xl_data.&_webin_sasname; %* set data values based on Excel table ;

run;

%if (&return) %then %do; %* all of this is for debugging ;

proc print noobs;

run;

proc contents data=&syslast;

run;

%end;

%mend;

options mprint nocenter;

%CreateDatasetFromExcelTable

This does an OK job of replicating the dataset that was filtered on the server by the SAS Data task and returned to Excel. I could then "chain" a 2nd stored process to work with that "uploaded" dataset and join in additional data, summarize data, etc.

But again, if I could generate a decent where clause from the stored process prompting mangler I would skip the SAS Data process altogether. Performance is a concern since 1) the query returns the result set to Excel (probably via XML under the covers, which gets parsed by the AMO addin, and 2) I turn right around and pass the result set back as XML for the stored process to create the same dataset on the server.

If there was a way I could "trick" the SAS Data process to 1) create the result set as a dataset on the server, and 2) not download the result set back to Excel, I'd do that. But, #2 defeats the purpose of the SAS Data functionality, so I can see why that's not baked into the product.

Thoughts?

Re: Where clause generator in a stored process using SAS Prompting Manager?

jakarman — Tue, 30 Jul 2013 16:55:37 GMT

Scott,

You are switching very fast, a real time traveller. asking with AF as background and than moving into the latest with office and VBA.

The prompting framework is something generic, but has not an own documentation (or not found yet).

I remember to have seen something in the stpug. SAS(R) 9.4 Stored Processes: Developer's Guide (Macro Variables That Are Generated from Prompts)

The chapter before that is telling someting about the contents and their meaning etc.

Re: Where clause generator in a stored process using SAS Prompting Manager?

Reeza — Tue, 30 Jul 2013 17:05:04 GMT

What about using the native excel tools to query? If you created a bunch of views that had your predefined join and somehow passed those over?