Desktop productivity for business analysts and programmers

Insert data in SQL Server table via ODBC

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Insert data in SQL Server table via ODBC

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. Smiley LOL


Accepted Solutions
Solution
‎11-09-2017 10:26 AM
Super User
Posts: 5,490

Re: Insert data in SQL Server table via ODBC

Posted in reply to mrcostaluis

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.

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,490

Re: Insert data in SQL Server table via ODBC

Posted in reply to mrcostaluis
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.
Data never sleeps
New Contributor
Posts: 4

Re: Insert data in SQL Server table via ODBC

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. Smiley Sad

Super Contributor
Posts: 334

Re: Insert data in SQL Server table via ODBC

Posted in reply to mrcostaluis

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.

Super User
Posts: 5,490

Re: Insert data in SQL Server table via ODBC

Posted in reply to mrcostaluis
Interesting, please describe how you so to get the data visible within SAS (specify if it's on SAS session or in EG).
Data never sleeps
New Contributor
Posts: 4

Re: Insert data in SQL Server table via ODBC

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.The File - Open - ODBC method.Then it shows this alert window, but it's okay for me.Then it shows this alert window, but it's okay for me.Icon of the database in the project.Icon of the database in the project.

I'm using SAS Enterprise Guide 7.1.

Solution
‎11-09-2017 10:26 AM
Super User
Posts: 5,490

Re: Insert data in SQL Server table via ODBC

Posted in reply to mrcostaluis

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.

Data never sleeps
New Contributor
Posts: 4

Re: Insert data in SQL Server table via ODBC

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 240 views
  • 0 likes
  • 3 in conversation