topic Re: Insert data in SQL Server table via ODBC in SAS Enterprise Guide

Insert data in SQL Server table via ODBC

mrcostaluis — Tue, 31 Oct 2017 12:49:15 GMT

Hey everyone,

I'm using SAS Enterprise Guide 7.1 and I need your help with something:

I already discover that it's possible to access an ODBC if it's was previously setup in SAS Server, but I can't do this here at work. So I saw an alternative: use the File4Open4ODBC works at least to see the data from the SQL Server table.

Now what I want to know is: is there a way to insert data in that SQL Server table using the method above? The way I did, it seems that SAS just read and put the SQL Server data in a dataset. Am I right? Please, tell me that I am not.

Re: Insert data in SQL Server table via ODBC

LinusH — Tue, 31 Oct 2017 16:25:58 GMT

I don't know about that File4Open4ODBC. But SAS can both read and insert data via ODBC.
But it requires SAS/ACCESS to ODBC to run seamlessly.

Re: Insert data in SQL Server table via ODBC

mrcostaluis — Wed, 01 Nov 2017 12:34:08 GMT

LinusH,

Thank you for replying me.

I think that the difference between using a libname to connect via ODBC and using the "File 4 Open 4 ODBC" method is that the second one can use your local ODBC connections.

Using "File 4 Open 4 ODBC" shows me the data from an SQL Server table in SAS, but I have no idea how I can insert new data in that table from SAS.

About that SAS/ACCESS to ODBC, is there a way to see if I have this?

Obs.: Sorry if my question is a little bit confusing, English is not my native language.

Re: Insert data in SQL Server table via ODBC

LinusH — Wed, 01 Nov 2017 16:50:52 GMT

Interesting, please describe how you so to get the data visible within SAS (specify if it's on SAS session or in EG).

Re: Insert data in SQL Server table via ODBC

mrcostaluis — Mon, 06 Nov 2017 15:09:28 GMT

LinusH, sorry for replying today, it was holiday in my country.

So, I uploaded some images that shows the process:

The File - Open - ODBC method.Then it shows this alert window, but it's okay for me.Icon of the database in the project.

I'm using SAS Enterprise Guide 7.1.

Re: Insert data in SQL Server table via ODBC

LinusH — Tue, 07 Nov 2017 22:17:39 GMT

From EG help:

"Inserting an ODBC data source

In order for SAS Enterprise Guide to process a task or query by using ODBC, it must first copy the data to the Enterprise Guide client computer

This tells me that the only functionality offered here is to copy data from that source into EG/SAS in order to process it. With that in mind, an no other section in the help around this integration, I conclude that there is no way to insert/update data in an ODBC source using this method.

It seems intended only for small extract for testing purposes only.

As said before, you need SAS/ACCESS on the SAS installation to perform this activity.

Re: Insert data in SQL Server table via ODBC

esjackso — Tue, 07 Nov 2017 22:46:57 GMT

Mrcostaluis

You might have to give more information on the SAS and ODBC setup you are using. You mentioned SAS server in your original post. Libname access and file menu access can both use local ODBC (I believe) ... but if you are using EG to connect to a SAS Server (or SAS GRID setup) where the processing is actually done you might be limited to what the server has access to through its connections. If you are just using EG as a front in to your local PC SAS install then you probably have less to worry about.

This code placed in a SAS program node and submitted should provide a list of the SAS components that are available to the session.

Proc Setinit; run;

If you see SAS Access to ODBC then you should have libname access to use odbc libname engine.

One thing to remember is that you are still limited by the permissions setup by the user associated with the ODBC connection. If it only has read only access they you will not be able to update tables in the DB.

Re: Insert data in SQL Server table via ODBC

mrcostaluis — Thu, 09 Nov 2017 15:25:51 GMT

LinusH,

I was afraid that this functionality only copy data from the SQL Server into SAS Enterprise Guide, but as you said, it's exactly what it does.

Thank you so much for your help!