Questions (or search strings) lead people to my blog or my email box on a daily basis and many of these are related to what SAS Stored Processes can or cannot do.
- Can a SAS Stored Process query an RDBMS?
- Can a SAS Stored Process create a data table?
- Could it create an OLAP cube?
- Can a SAS Stored Process create a PDF file (tip: use _ODSDEST), how about an Excel file?
Thankfully all I have to say is 'YES!', if a SAS program can do it then so can a SAS Stored Process (and with little to no tweaking). That is what makes SAS Stored Processes so incredibly awesome, the SAS developer can switch roles - from a bottleneck 'Run this job for me now' person to an enabler, creating reusable content that anyone can use immediately and with a couple minutes of click-this training.
Just remember, because all .sas files COULD become SAS Stored Processes doesn't mean that they all SHOULD become SAS Stored Processes. For example, .sas files that need to be run automatically and/or require no user interactions are not necessarily candidates for a SAS Stored Process. But .sas programs that require some modification to run for a specific need and/or are run when requested are absolutely good candidates. Can you think of other .sas programs that should (or should not) be considered for the SAS Stored Process job?
For more examples of what SAS Stored Process programs look like and can do, check out The 50 Keys to Learning SAS Stored Processes book. There are 35 SAS Stored Process examples covered and key after key of developing them.
6 Comments
Hi Angela,
I'm a experienced SAS programmer, but brand new for stored process. Our company is using Enterprise Guide 4.3. I have tried to create my first stored process from SAS EG. I can put the 'Name', 'Location' at 1 of 6 of 'Create New SAS Stored Process Wizard', then I can see my SAS code shows up at 2 of 6. I got trouble at 3 of 6: Execution Option: When I choose any execution server from the list, I got a '!' sign shows up, the message is 'Unspecific Error'. Any advise please?
Thank you in advance!!
Hi, Angela
Excellent post! Stored processes are truly one of the "missing links" that turn SAS from a personal tool into a corporate information utility.
You alluded to the "just because you can do it, doesn't mean you should" principle. I agree completely, and with regards to stored processes I'd like to add my thoughts:
The purpose of building a stored process is to make SAS code reusable by other users, possibly with parameters. Because of this, a developer needs to shift from the personal code (a spreadsheet that figures out how many days to retirement!) to a mode of structured development for business clients. Some of the things that need to be considered:
Is the submission process clear? What might be obvious for you when you submit it has to be acceptable for someone who knows nothing about your thought processes.
What if something goes wrong? I'm used to debugging from a log, but I don't want to impose that on business users. The code needs to be bulletproofed.
What if the input data are bad? You can know that your data is fine, but a remote user might not understand the constraints. What if your SP returns results, but they're incorrect?
All of these will increase the costs, so it's important to make sure that your SP will deliver enough business benefit to justify the extra costs of development and testing.
Agree? Disagree? What do you folks think?
Tom
Thanks Tom! I love your 'bullet-proof' comment. This is completely true.
Some recommendations on bullet proofing your STP would be to actually improve your prompt. Tricia Aanderud writes about prompt setup at her blog BI-Notes.com. Also, in my new STP book The 50 Keys to Learning SAS Stored Processes there is another idea for improving the user's experience - an example for returning a message that 0 records were returned rather than showing an empty page.
hi Can we convert JMP in to stored process and can u just give simple example
Manas,
My understanding is that you would need to save the JMP project as a .sas file and then register that code as a SAS Stored Process.
You might consider posting over on the JMP Discussion Forum to get other tips concerning JMP.
Here is one example: https://communities.sas.com/message/31977#31977
~ Angela
Pingback: Use OLAP data sources from SAS Stored Processes - Real BI for Real Users