Executing SQL Stored Procedures Using PROC SQL
The following examples demonstrate the basic syntax required to run a SQL Stored Procedure using SAS PROC SQL.
The examples assume the following prerequisites:
- The relevant ODBC data source and OLE DB connections have been setup
- Valid user credentials which can access the stored procedure on the database are being used.
Simple Execution of a SQL Stored Procedure
The below example simply executes the SQL stored procedure:
Typical uses of the above include:
- Part of a SAS job flow, where both SQL and SAS programs work within the same job flow
- Where a SAS output of results is not required.
Passing Parameters and Outputting Results
Below is a further example, alternatively using OLE DB. It builds on the first example by:
- Passing the SQL stored procedures parameters prior to execution (in this case date parameters @start_date & @end_date)
- Outputting the SQL stored procedure results to a SAS dataset.
Typical use of the above include:
- Solutions which have already been developed at the SQL end and only require SAS to utilise the stored procedures parameters, rather than expend additional time developing a second SAS solution
- Where a SAS output of results is required.