BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have a stored procedure that creates a table. I want eg to execute the stored procedure so i can link the database table into eg.

How does one execute a stored procedure from EG?
1 REPLY 1
deleted_user
Not applicable
I am still learning both SQL and re-aquainting myself with SAS via SAS EG/LE. I haven't had time to actually run this code but here is what my research seems to indicate.

I just got done looking at the "PROC SQL essentials code samples" available here on the website. It appears you mostly use the exact same code you would in an SQL editor window to execute the stored procedure.

Below is a sample of executing some SQL against an Oracle database that is located on the same pc as Enterprise Guide. Below that is guess at an example of code for a stored procedure and executing it in Ms SQL.

PROC SQL;
LIBNAME bluesky "C:\mydata\bluesky bookstore\";

CONNECT TO ORACLE
(user=scott password=tiger path=master);

SELECT *
FROM CONNECTION TO ORACLE

(SELECT currency "Currency", avg(exchrate) "Average",
max(exchrate) "Maximum", min(exchrate) "Minimum"
FROM bluesky.currency
WHERE currency IN ('CAD','GBP','DEM')
GROUP BY currency
);

DISCONNECT FROM ORACLE;

QUIT;
------------------------------snip-----------------------------------------------------
Here's the MSQL sample code I found outside of SAS.com code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'
---------------------------------------snip--------------------------------------------------------

So the code that you want appears to be:

PROC SQL;
LIBNAME bluesky "C:\mydata\bluesky bookstore\"; <- local database location I think

CONNECT TO MSQL <- this might not be the correct name for ms sql connector
(user=scott password=tiger path=master);

EXECUTE sp_GetInventory 'FL' <- whatever you called the stored procedure plus any parms you need to pass in.

DISCONNECT FROM MSQL;

QUIT;

-------------------------------------------snip-----------------------------------------------

Try it out, generate some errors and come back and tell us about them 🙂

Tom Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 4820 views
  • 0 likes
  • 1 in conversation