04-09-2014 05:48 AM
I have a question regarding a SAS Automation with using a prompt interface for filtering. But before coming to the question, I have some details about the environment:
- SAS Base 9.4 with using the application SAS EG 6.1 (Server on Windows Server Edition 2012 and local machine running with Windows 7) and the Microsoft Office Add-In 6.1
- Sybase database connection
Currently I receive requests to extract certain detailed data files for the end user with SAS, e.g. data sets with even more than 1,000,000 data lines and I want to allow the user to run the request by their own. The end user shall not interfere in any way with SAS (as much as possible).
I have tried out certain ways of enabling the user to this data request:
- First, I created a stored process to extract the data and add it to MS Excel via 'List Data' and ODS Output. The data lines are already split into certain Excel tabs to avoid the maximum capacity in Excel (not more than 1 mn lines per tab). However, the Excel file can now increase up to 740 MB, which is not suitable at all for the end user.
- As a second option, I wanted to schedule the project as a vbs-file. This is working properly, also with the Export Task for a csv-file. But unfortunately, I have included a prompt for filtering and it will only take the one from the initial stored run. In addition, it seems only be possible at a local drive and not on a shared drive. (Remark: Currently, I am not allowed to use the SAS Management Console as the SAS set-up is still in a project phase and the company wants to the split the Admin work from the analysts)
- The third option would be an auto executed process, while opening the SAS EG project. Unfortunately, the end user can mess up the project with additional or modified queries and I want to disable the end user from this task.
At the end, I would like to have an auto executed SAS project with no obvious interaction with SAS.
Do you have any suggestion for me?
Thank you very much in advance J
04-09-2014 07:13 AM
How about implementing it as a stored process? The user only gets prompted and then the result(s).
04-09-2014 08:39 AM
I thought the same but it will not help us. I do not want to start an "export-to-csv-process" e.g. within another Excel file. Then I started it in Outlook, that seems fine. However, other users struggle with the SAS Add-in in Outlook, hence we disabled the function the regarding users.
Can we start a stored process other than via a Microsoft Product? Or do you have another idea?
04-09-2014 08:51 AM
You could run the Stored Process with the Stored Process Server and let it write the csv to a location where the user can pick it up.
04-09-2014 08:57 AM
Do you have any material, how to set-up the stored process server?
Now, I have to convince IT to set-up this server, and I would need some pro's and con's.
04-09-2014 09:29 AM
You can find documentation in the Web Application Administration Guide and the Application Server Administration Guide.
The Stored Process Server is defined in Metadata and started by the Object Spawner.
The Stored Process Application is part of the package that is deployed with the Enterprise BI Server and runs on a Web Application Server, usually JBOSS. That way the user only needs a browser to run a stored process.
04-09-2014 09:44 AM
@kurt, A stored process can be run every SAS client Eguide AMO and by Web clients (although there are differences).
This is the web approach: SAS(R) 9.4 Stored Processes: Developer's Guide (Overview of Stored Process Web Applications)
These are all the clients: SAS(R) 9.4 Stored Processes: Developer's Guide (Which Clients Can Use SAS Stored Processes?)
This is how to build them Eguide is probably the best tool to do that. SAS(R) 9.4 Stored Processes: Developer's Guide
04-09-2014 09:54 AM
Yes, I used EG to develop a bunch of SP's that are designed to replace an old AppDev Studio Web App that we did back in 8.2 times. But I also had to do some work in HTML to get nice pages for the prompts that completely hide the SAS infrastructure behind that.
Of course the whole infrastructure (Remote Services, JBOSS, Web Apps) needs to be set up first, which may prove to be a major obstacle for the OP.
04-09-2014 10:15 AM
@kurt, the midtier (jboss Rmi webapp) is not needed when running Stored processes by Eguide/Amo. These clients are not web-based on that level. I know it is very confusing...
In the case of s stored process server there is only a need to add that (restart object spawner) to the metadataserver. As OS files are created I would advice to use the related wizard for that.
To get really confused, when you would like to run Enterprise Miner with the JAVA-desktop client you are needing the midtier.
This product is just using the same Workspace Server as Eguide that one focused as personal processes.
04-09-2014 10:25 AM
Yeah, I hear you
But the OP made it quite clear that he doesn't want to confront his users with something like EG.
Given a large enough number of users, simply installing EG can prove to be a major PITA (I know from bitter recent experience).
04-09-2014 10:37 AM
I admit, that is where you right with that PITA experience. The problem not every tool is the right tool for all persons
- Eguide is just suited for advanced business analysts and programmers including the staff supporting that.
- Amo is suited for the occasional user but you need supporting staff. The OP made already a remark this was causing issues and stopped this.
- Web-based the old SAS-portal and the new SAS-VA should do the work for newbies in SAS. Kurt I still agree with you.
Problem: The OP did not mention this in his environmental description he is rolling out.
Fun my brain switched to holidays as pita being translated to bread. :smileysilly:
04-09-2014 09:14 AM
Understanding some things about the basics in the EIP approach will help.
The sizing of 1M record is still small for SAS that is not your biggest concern to worry about.
The workspace server is normally bind to interactive personal actions, Eguide DI AMO etc. by hat the identity of the SAS process behind would be logical your personal account.
As IT people are often very unlikely accepting the changes that are needed by this SAS approach they often are violating the normal IT policies by changing SAS. It could be they are using a shared generic account for this.
The stored process Server however is designed to run a shared generic account. SAS does the switching on the user context. When you have sensitive data this switching could be a concern for auditabity and traceability. If you have that one setup you can also think on release-management. Only allowing approved tested programs to be run by your users.
Having that in place you can think about scheduling. Scheduling of Stored process are essentially scheduling SAS programs, SAS(R) 9.4 Stored Processes: Developer's Guide (proc STP)