BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrcostaluis
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

7 REPLIES 7
LinusH
Tourmaline | Level 20
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
mrcostaluis
Calcite | Level 5

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

esjackso
Quartz | Level 8

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.

LinusH
Tourmaline | Level 20
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
mrcostaluis
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20

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
mrcostaluis
Calcite | Level 5

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!

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
  • 7 replies
  • 3102 views
  • 0 likes
  • 3 in conversation