topic Re: Can Enterprise Guide Pass a SAS Table to Oracle? in SAS Programming

Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Tue, 22 Jun 2021 14:47:44 GMT

Can Enterprise Guide pass through a SAS table to Oracle? I didn't find the syntax online. Can someone please help? Thanks.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Reeza — Tue, 22 Jun 2021 14:59:57 GMT

Assuming you have permissions to create tables in your Oracle server, yes.

How are you typically connecting to your server, via explicit pass through or do you have an ODBC connection set up?

@Dee2 wrote:

Can Enterprise Guide pass through a SAS table to Oracle? I didn't find the syntax online. Can someone please help? Thanks.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

NicoM — Tue, 22 Jun 2021 15:48:24 GMT

Yes. If you can define a SAS library to the Oracle database this is possible. In that case you can use a regular data step or PROC SQL to create a table (assuming that you have permissions to create tables) in that library and it will appear in the database.

If you do not have permissions to create tables on the database it is still possible that you are allowed to insert records into a table.

Do you have a SAS library assigned to the Oracle database?

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Tue, 22 Jun 2021 17:34:09 GMT

I don't know if there is a SAS library assigned to the Oracle database. Do I find out from the SAS side or Oracle? Thanks.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Reeza — Tue, 22 Jun 2021 17:43:14 GMT

Since you want to push the data from SAS to Oracle, you need a connection in SAS to Oracle. You would check in SAS for the connection.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Tue, 22 Jun 2021 19:26:52 GMT

Thank you.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

SASKiwi — Tue, 22 Jun 2021 20:11:28 GMT

You also need a SAS product that can connect to Oracle installed like SAS/ACCESS to Oracle or SAS/ACCESS to ODBC. You can confirm if you have such a product installed and licensed by running this:

proc product_status;
run;
proc setinit;
run;

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Wed, 23 Jun 2021 14:36:20 GMT

Thanks. I think it does. I saw the expire date.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Sajid01 — Wed, 23 Jun 2021 17:08:15 GMT

Hello @Dee2
1. You wanted to know how to pass SAS table to oracle.
The following reference has all the details and will hopefully solve all your problems.
https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/106-29.pdf

2.Your second question is

"I don't know if there is a SAS library assigned to the Oracle database. Do I find out from the SAS side or Oracle?."
The simplest approach for an answer to this question ask your team / manager.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Wed, 23 Jun 2021 20:35:19 GMT

Thank you. I will check out the link.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

SASKiwi — Thu, 24 Jun 2021 02:03:55 GMT

@Dee2 - Do you have SAS administrators available to you? They should be able to tell you if they have EG data libraries pointing to Oracle or not or at least guide you how to do it.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Dee2 — Thu, 24 Jun 2021 16:41:04 GMT

Yes, I will. Thanks. I thought it might be a temporary quicker approach before asking the administrator. 😀

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

SASKiwi — Thu, 24 Jun 2021 20:28:39 GMT

@Dee2 - I'm a SAS administrator where I work and when we create data libraries for all SAS users in SAS Enterprise Guide and they point to an external database we add SQL on the front of the name so it is obvious they aren't SAS dataset libraries. Perhaps you could suggest a similar approach with your administrator.

Re: Can Enterprise Guide Pass a SAS Table to Oracle?

Sajid01 — Thu, 24 Jun 2021 22:03:43 GMT

Well then you must create the libraries in the SAS Management console and preassign them or alternatively use autoexec_usermod.sas..
Second about the original question of passing SAS tables to oracle, have a look at the link that I shared in my earlier post.