The Stored Process Wizard may be used to generate a stored process from an SAS ETL Studio job. SAS Enterprise Guide enables the development, modification, and testing of stored processes in a unified environment. This blog post demonstrates how to generate a stored procedure from an ETL Studio task.
A stored process can be created from a SAS ETL Studio job using the Stored Process Wizard.
Because most sas etl studio jobs do not create ODS output, stored processes created from SAS ETL Studio do not include the %STPBEGIN and %STPEND macros. If the SAS ETL Studio job does create ODS output, the %STPBEGIN and %STPEND macros can be added manually to the program created by the Stored Process wizard.
The Stored Process wizard saves the job in the file system using the specified name and source repository. Metadata is also written to the metadata repository describing the stored process.
The stored process can be deleted or modified with SAS ETL Studio by
In order to run this stored process, the user must have authority on the operating system and the metadata to update the OrderFact table. If the stored process is running on a Stored Process server, the user ID is sassy. If the stored process runs on a Workspace Server, it runs under the ID of the client.
This demonstration shows how to create a stored process from an existing ETL Studio job.
Start SAS ETL Studio by selecting Start Ð All Programs Ð SAS Ð SAS ETL Studio, select the BIArchitecture metadata profile and enter the username and password provided by the instructor.
2. Expand Foundation in the Custom Navigation Tree.
3. Expand the Jobs group.
4. Right-click on Populate the ProductDim table and select Stored Process Ð New….
The New Stored Process Wizard opens.
5. Select to add a new folder.
6. Name the new folder ETL Stored Processes.
8. Select the ETL Stored Processes folder and select
9. Type Load the ProductDim Table as the name of the new stored process and enter a description and keywords (if desired).
10. Select SASMain – Logical Workspace Server as the SAS server.
11. Select and create a new location for the Source Repository by selecting and entering C:WorkshopwinsassbiovrETLJobs as the Location.
The directory C:WorkshopwinsassbiovrETLJobs must exist or be created on the operating system before completing the New Stored Process Wizard.
12. Select C:WorkshopwinsassbiovrETLJobs as the Source Repository.
13. Type the load productdim_sas as the Source file name.
This .sas file will be created in the specified location by the New Stored Process Wizard.
14. Select None as the Output type (default).
15. Because there are no parameters for this stored process, select
16. To edit the stored process, right-click on the job, select Stored Processes Ð stored process name ÐProperties….
17. To delete the stored process, right-click on the job, select Stored Processes Ðstored process name Ð Delete.
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
Vinod M is a Big data expert writer at Mindmajix and contributes in-depth articles on various Big Data Technologies. He also has experience in writing for Docker, Hadoop, Microservices, Commvault, and few BI tools. You can be in touch with him via LinkedIn and Twitter.
Copyright © 2013 - 2022 MindMajix Technologies