topic Re: SAS writer in SAS Enterprise Guide

SAS writer

AmitSingh — Thu, 25 Aug 2016 06:26:45 GMT

Hi,

We have a requirement in which we would like to write data in Oracle database tables through SAS EG. In our current environment we have connections between SAS and Oracle database, but these connections are meant for reading data from oracle database and then some calculation are done in SAS EG and then some excels are generated manually.
We would like to automate this process like :-

Oracle(Read data through SAS EG) ---> SAS EG(some calculation done here)-----> Oracle(Write in Oracle through SAS EG)

We have tried to do the above scenario by making below changes and it worked:-
1. Go to Oracle library defined in SAS MC
2. Go to Options --> Advanced_Options-->Input/Output
3. Change value of "Data access level for a connection" from READONLY to Blank(no value).

This allow us to write data through SAS EG.
But we would like to check with SAS if there is some other better way to do this or this is the only solution.

Re: SAS writer

Kurt_Bremser — Thu, 25 Aug 2016 06:31:01 GMT

For libraries defined in SAS metadata, this is exactly how you should proceed. Change the parameters where they are defined, and have one single point where such information (metadata) is kept and worked with.

Re: SAS writer

RW9 — Thu, 25 Aug 2016 08:30:17 GMT

If you go down the route you state, you may end up in a feedback loop. I.e.:

Oracle data is updated independantly

You extract data to EG

You make modifications

You upload to Oracle

Now at this point, the data in Oracle might be different when you upload, or later on the database process/entry person notices data has changed and changes it back again. This is a feedback loop where you change it, and because of that, the DB changes it, and then you have to change etc. Fix your process, send changes to the database owner for them to implement, you should only read the data.

Re: SAS writer

AmitSingh — Thu, 25 Aug 2016 10:27:35 GMT

Sorry I did not get your point.

"Fix your process, send changes to the database owner for them to implement, you should only read the data."
Could you please explain more

Re: SAS writer

AmitSingh — Thu, 25 Aug 2016 10:28:55 GMT

Also would writing data in Oracle would require some additional licences ?

Re: SAS writer

Kurt_Bremser — Thu, 25 Aug 2016 10:37:00 GMT

@AmitSingh wrote:
Also would writing data in Oracle would require some additional licences ?

No. SAS/ACCESS allows writing without further licensing.

Re: SAS writer

Kurt_Bremser — Thu, 25 Aug 2016 10:38:50 GMT

@AmitSingh wrote:
Sorry I did not get your point.

"Fix your process, send changes to the database owner for them to implement, you should only read the data."
Could you please explain more

Changes in the DB should not be made directly out of SAS. Instead send a delta table or similar to the DB owner, who then decides how and when the changes will be implemented.

Re: SAS writer

RW9 — Thu, 25 Aug 2016 10:42:24 GMT

As @Kurt_Bremser has mentioned. You are not the owner of the database. The owner of the database should be making updates to the database. You are a consumer of that data. There is one owner, and many consumers.

Re: SAS writer

AmitSingh — Thu, 25 Aug 2016 11:18:30 GMT

Okey. I got the point. Thanks!!
We have dedicated db users defined who can make the changes in database. So all the changes will be done with consent with DB owner.

Re: SAS writer

Patrick — Fri, 26 Aug 2016 00:36:06 GMT

@RW

What you describe is only one possible scenario applicable to a data warehousing scenario and may be not applicable here.

If the database owner wouldn't want to allow updates to the tables then I'd hope the Oracle grants would be set accordingly.