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.
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.
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.
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, sorry for replying today, it was holiday in my country.
So, I uploaded some images that shows the process:
I'm using SAS Enterprise Guide 7.1.
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.