SAS EG 5.1 STP output to Excel, JMP etc

[FredGIII]

Hi everyone,

Just getting started with SAS and stored processes. I have question on generating outputs from a stored process created in EG 5.1.  First off, I have googled my #$$ off and documentation seems to be lacking or I am not finding it. I see lots of documentation covering writing the STP code, but I would appreciate it if any one can point me to clear examples or documentation of using EG 5.1 to create a stored process that can be run by Excel or JMP and auto-populate with the data results from the STP. 

I created a simple EG process flow which uses prompts to filter down a couple of larger data sets into a single table.  I then created the STP from that and modified the prompts to provide cascaded prompts with dependencies. All that seems to work, but when I run the STP, it spits out an empty SAS Report. Not clear as to why. I assume it has something to do with Data Sources and Targets - but again, the documentation on this topic is worse than anemic. Any clear examples on setting this up for different types of data targets would be appreciated. Generally speaking, we are mainly concerned with pushing data to either JMP or Excel.

Thanks,

Fred

[Cynthia_sas]

Hi:

  It really comes down to what your process flow was doing and how EG turned that into a Stored Process and how the metadata for the stored process was defined. In our classes, we have examples of running a stored process and successfully returning the results to Excel (many different examples, in many different classes). And there are documented ways of running a Stored Process in JMP (assuming all the right configurations). Have you looked at the Stored Process Developer's Guide? Getting a stored process working with Excel is not difficult, which makes me suspect that there's something in your log that should be a clue to the problem and if there's nothing in the log, then something in your stored process settings in the metadata probably need to be adjusted. This is probably one of those things that you need to have Tech Support look at. They can look at ALL your code, generated by creating the Stored Process from the Process Flow; and help you figure out the metadata settings and then help you debug getting it working with Excel and then move on to getting it working from inside JMP.

cynthia

[FredGIII]

Hi Cynthia,

Yes I have looked at the STP Developers Guide (9.3), but as I said before, it seems to be targeted to writing the STP code and not creating a STP from an EG process flow.  I have been reading everything I can both in the SAS documentation and the web. Keeping in mind, we just got our sas server set up this week, so it's only been a few days.  I will definitely be signing up for a STP class (recommendations of specific classes welcome).  But in the mean time, I am testing the waters with some simple STP's. 

What my first process flow is doing:

Working through a series of cascading prompts with dependencies to select a specific device and then joining that result with a larger fault data table and then prompting for a date range to filter on. The resulting table is a list of faults for that specific device over a filtered date range.  The process flow works. 

How EG turns that into a STP?

Not sure what you mean, unless you are referring to the wizard and the various screens. On pg3 of the wizard I select SASApp as the application server, Server type = default server, Source Code location & execution = allow execution on other app servers(store src code in metadata), Result Capabilities = Package

Pg4 - librefs are set appropriately for the reference tables

Pg5 - Prompts - edited the prompts to include dependencies.

Pg6 - Data Sources & Targets - currently nothing set (no need for Data sources, since sources are local datasets)

I have looked for documentation on the Data Targets and setting those, but the documentation seems thin and ambiguous.  Not clear if and how to set this appropriately for various targets (ie. JMP & Excel)

When I save and run the STP from EG, the cascading prompts work as expected, based on the log, everything appears to work, but the output of the STP in EG is a blank SAS Report.  I can contact Tech Support, but I was rather hoping there would be documentation or examples more directly related to STP in EG 5.1 and more details on the wizard settings.

Regards

Fred

[Cynthia_sas · Accepted solution]

Hi, Fred:

  Let's make sure a simple stored process runs. Try this in EG...create a new stored process. When you get to Step 2, cut and paste this in:

proc print data=sashelp.class;

title 'This is being deliverd by a stored process';

run;

  Take all the same defaults for the other steps in the wizard.
        
  Then, run the stored process within EG. Do you see results (should be 19 observations). If that works, then run the stored process from within Excel. Does that work? This simple stored process will return 10 obs in a report form to EG and in spreadsheet form to Excel. and if you run the SP in Word, then the 19 rows will appear inside a Word doc. When you run a stored processes, the most frequently created type of output from the stored process is a report that can be opened and rendered in the client application. The PROC PRINT step creates "visible" output that can be opened by the client application after the stored process runs.

    
  Normally, if the end result of your process flow is a table, or dataset, there's nothing for the stored process  to return to the client application. Usually, you need to run a LIST DATA  or some other report task on the table in order to make sure that the SP has results to send to the client application. You did not mention any log messages. I'm assuming there are none.  But does the log show that your table was created?

      
  The typical way that stored processes work is that they return "visible output" of some kind -- results of a report task or an analytic task to the requesting client application. So let's assume a different kind of project. Let's say that you create a project that only has one task and the task is a sorting task. In other words, you open a dataset, sort it and maybe filter during the sort. So in this type of project/process flow, there's only the sort step, there's no "visible output" -- only a table. And, yes, I know that within EG you can "see" the  table. But, EG is being nice to you by opening that Table Viewer.

  I would not expect the second stored process that I describe -- the one with just the sort to "do" anything for you.

  You will find some of this out in the Stored Process classes:

Stored Process 1: https://support.sas.com/edu/schedules.html?ctry=us&id=1817
Stored Process 2: https://support.sas.com/edu/schedules.html?ctry=us&id=1840

And, in the Information Map class, we show how to populate an information map with the data results from a stored process...Information Map class: https://support.sas.com/edu/schedules.html?ctry=us&id=1887

  Those are just some suggestions. I really urge you to open a track with Tech Support. They need to look at the code generated by your Process Flow and your stored process metadata to see where your stored process is going astray.

cynthia

[FredGIII]

Cynthia,

Thanks! Your tips were invaluable. You were right about the output in EG. I was assuming that since the data was being displayed in EG, that it would be the same data pushed to the clients. Once I created an output report, then it worked!

Thanks again.

[jakarman]

Running a STP developed with Eguide within Excel is easy when using the add in Microsoft Office AMD.

You did not mention that tool... Why not?

[FredGIII]

Jaap,

I am using the SAS Microsoft Add-in for Excel.  I can run the STP from Excel, that part is easy, but not getting any results pushed to Excel. I was trying to push the data table results to Excel using the STP , but I get nothing. So far, I am not seeing any clear documentation on settings with the EG STP wizard to enable the results to be pushed to Excel or JMP.

Regards,

Fred

[jakarman]

As other remark local resources with eguide are non existent.

Either you have a local sas base also connected to eguide access in local resources on your desktop or you are running sas server based with a metadata server.

Having a sas server with metadata there should be a job for a sas platform administration doing all kind of work from architectural concepts to user and data administration with support to your operating system and business policies.

I this role has not been foreseen you have your omission to solve now.

[FredGIII]

Jaap,

oh just contact the the sas platform admin.  LOL Everyone makes that assumption - that if you have SAS, it is a big production oriented system with dedicated admin team. Unfortunately, that is not our case. We are using SAS in an engineering environment. We are working on solving engineering and ad-hoc problems using data analysis with SAS.  Because of this, we are a very small team of engineers who convinced management to have IT stand up servers for us to use for our SAS server configuration.  Our IT group is "difficult" to work with to put it mildly.  Other than standing up the servers, they have nothing to do with the servers from a SAS perspective. We are our own admin (with no training).  So learn as we go. We have convinced management to include budget next year to contract out the SAS admin function - but that is next year. So back to my original question....  

[jakarman]

Fred, use the samples datasets in sas help.

These are available to everyone (read only).

You can access them from excel with amo.

If you want a so delivering data in a similar way there must be a location to have stored that data by the ST process and being available by the amo sas process all running sas but probably running in different usercontexts.

The data sas libraries and sas files Libname filenames must be made available by configuring the appserver (sasapp).

Needing different user groups it will probably result in more sas appservers.

The documentation start are the platform administration guide and the security administration guide. This technical development is quite different to business application development. The building of SP''s is Business application.

Who is doing the platform administration role for you?

[jakarman]

Fred, the fun of the platform administrator role has been caused by sas.

You have to solve that now on adhoc.

Keeping things as simple as possible should help.as it will introduce possible other issues.

Configuring the usermod files autoexec en config script of the sasapp sas server will probably give you the quickest results. They are working for the workspace server and stored process server for all usercontexts.

Using the sashelp dataset is giving you samples how to work with data.

Stored process generating results are putting that output.

Without results defined there will be no results