Desktop productivity for business analysts and programmers

SAS writer

Reply
Contributor
Posts: 22

SAS writer

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.

 

Esteemed Advisor
Posts: 6,685

Re: SAS writer

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: SAS writer

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.

Contributor
Posts: 22

Re: SAS writer

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
Contributor
Posts: 22

Re: SAS writer

Also would writing data in Oracle would require some additional licences ?
Esteemed Advisor
Posts: 6,685

Re: SAS writer


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

No. SAS/ACCESS allows writing without further licensing.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 6,685

Re: SAS writer


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: SAS writer

As @KurtBremser 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.

Contributor
Posts: 22

Re: SAS writer

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.
Respected Advisor
Posts: 3,837

Re: SAS writer

@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.

Ask a Question
Discussion stats
  • 9 replies
  • 434 views
  • 1 like
  • 4 in conversation