Precode on Stored Process

[BhavikShah]

Hi All,

On my stored process I have a prompt that uses a sas data source to get a dynamic list of values.

My specific requirement is that the values in this sas data source should be a list of objects that is created by this in the last few minutes at the . I have a code to update this table by looking at the metadata, but my problem is I want to run this bit of code when the user starts the stored process (something like a precode that we have on DI jobs).

Is this possible? Any alternative solutions would be highly appreciated.

Thanks,

Bhavik

[Haikuo]

I will be very interested to know the answer as well. What we did for the same purpose is ask users to run two STPs, First one to use "Proc meatlib" to have the newly created table registered/updated in metadata. Then the second one is the STP with the prompt.

Haikuo

[Quentin]

Seems like maybe you should separate the "pre-code" (update the sas data source used to define prompt) from the stored process.

You have a SAS data source that is being used to define dynamic promts.  Call this dataset PromptData.

Sounds like you have some other data source with prompt data (ExternalPromptData), which you are using to build the PromptData dataset.

So in your plan, when a user opens a reporting stored process, the pre-code should read the data from ExternalPromptData, and write it to PromptData, then use PromptData to lookup the prompt values.

Haikuo's approach makes sense, have an "updatePromptData" stored process.

But seems like perhaps you could have a separate DI job (or whatever), with a file watcher, so that every time there is a change to ExternalPromptData, it updates PromptData.  Depending on how often ExternalPromptData is updated, that could work.

--Q.

[Tom]

I don't use stored process but can they read from a view instead of dataset?

For example to only see the records from MYLIB.MYDATA where the variable LASTMOD (datetime) is within the last hour use something like this:

data mylib.myview / view=mylib.myview ;

  set mylib.mydata ;

  where datetime() - lastmod < '01:00't ;

run;

[Quentin]

Hi Tom,

Yes, I think the prompt data can be read from any "table" that is registered in the metadata. So could be an actual SAS dataaset or view or a table in a relational datbaese ets.

Sometimes it's easiest to just register ExternalPromptData.  However if ExternalPromptData is huge and/or far away in network space-time, there can be efficiency gains to creating a smaller dataset which just has the unique values to be used for the prompt, and is closer to the SAS server.  Otherwise, every time a user opens a stored process, SAS has to do a select distinct on the big table to get the list of prompt values.

--Q.

[BhavikShah]

Thanks for the reply guys.

HaiKuo and Quentin - Unfortunately cannot implement the method of having 2 separate stored processes as the users are completely against that idea.Also cannot implement the file watcher logic 1. As the frequency of the number of objects created is going to be high. 2. This objects are present only in metadata they are not present physically on disk.

Tom - The code is not an issue. It is the point in time when this code is executed (i.e at the start of stored process) which is of a concern to me.

[Quentin]

Sorry, can you repeat the initial question?  Looks like your first post may be missing a few key words.

What is the dynamic prompt you want to define?

Is it a list of datasets that have been recently created/updated?  Or something else? Where are these data stored?  What does your pre-code do?

If it's a list of recently created datasets, I wonder if you could register a view pointing to dictionary.tables or sashelp.vtable and use that as a source for your dynamic prompt?

[BhavikShah]

To answer your questions -

1. Those are Risk Explorations that the users would be storing in metadata.

2. The precode I have runs through the metadata and get the details about risk explorations created in metadata and makes an entry of all the latest risk explorations in a sas dataset. And it is this sas dataset that I use for the prompt to display the user with a list of explorations.

Thanks

[Haikuo]

In term of the table that generates the dynamic list, If no new table to be generated and the table structure (variable properties) remains the same, I don't see the problem for your end users to go ahead using one STP with prompts.

While if that table does change in a way that metadata needs to be updated, question is how often your data is updated. Can you manage to schedule the first STP/batch/EG project to run right after it has been updated? Then the end users will only have to deal with the STP with prompts.

The way that prompt works requires all of the elements for the prompt , dynamic or not, to be ready for input/selection before the STP can be processed.

Haikuo

[BhavikShah]

Haikuo - My table definition is not going to change but the list of entries it would show to the user is going to change and that is why I need to update the data within the table before the STP is started so that the users always have the current list in the prompt.Hope this makes sense.

[Tom]

That is why you should link the prompt to a view, so that the results will be current whenever it is pulled. 

[Quentin]

Agree with Tom, a view or infomap sounds like it could be what you need.  Down side is you have to process the underlying data every time the stored process is openeed, but if the data isn't huge, the cost may be low.

[BhavikShah]

Thanks Guys. We have decided to go ahead with with the approach of having chained stored process.