Desktop productivity for business analysts and programmers

Introduction, EG + Oracle + recursive programming

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Introduction, EG + Oracle + recursive programming

Hi everyone, first of all, let me introduce myself.

My name is Marcos Oba and I already programmed in SAS some years ago. I built a MILP optimizer that was a standalone solution (all data was feed from txt files and all results was exported to txt files).

Now I have a new challenge: I need to build a MILP optimizer that reads data from a Oracle DB, manipulate these data myself (in the first project, all data was received ready to process), build scenarios and run these scenarios recursively. In another words, using some algorithms, run small optimizations in loops to get near global optimal solution (the reasons to run it recursively and not a global optimization are due to the first modeling tool – Lindo´s Whats Best, and the main directive now is to implement it as is in SAS).

So, there are 2 main tasks that I´m not familiar:

  • Read, manipulate, and write Oracle DB´s
  • Program a recursive process

I never worked with Enterprise Guide (always a code direct in SAS with a text editor), but this does not look an issue. But I´m not familiar with the required connections to make it work.

Anybody can show me the path, or at least what documentation I need to follow? Some examples are really welcome!

Sorry for the poor English.

Regards,

Marcos


Accepted Solutions
Solution
‎12-03-2012 03:19 PM
Trusted Advisor
Posts: 1,059

Re: Introduction, EG + Oracle + recursive programming

I can help with the Oracle side of it; I've done this a bunch.

First, you have to make sure that your site is licensed for and has installed SAS/Accss for Oracle, as this is what SAS uses to communicate with the DBMS.

Then, make sure that the Oracle client software is installed on the SAS server that EG will be using, which will either be your PC or a remote server. It's best to test that connection can be made to Oracle from that machine using SQL*Plus, this will also verify that your account works.

At this point, you should be able to connect from Enterprise Guide to Oracle by either writing an Oracle libname statement in the code editor, or using the "Tools | Assign Project Library" menu items.

From here, your Oracle environment should look like a SAS library, you can read and write Oracle tables from SAS.

The SAS/Access documentation is online, and it's pretty good. Start with the basic options, and then optimize as problems come up.

  Tom

View solution in original post


All Replies
Solution
‎12-03-2012 03:19 PM
Trusted Advisor
Posts: 1,059

Re: Introduction, EG + Oracle + recursive programming

I can help with the Oracle side of it; I've done this a bunch.

First, you have to make sure that your site is licensed for and has installed SAS/Accss for Oracle, as this is what SAS uses to communicate with the DBMS.

Then, make sure that the Oracle client software is installed on the SAS server that EG will be using, which will either be your PC or a remote server. It's best to test that connection can be made to Oracle from that machine using SQL*Plus, this will also verify that your account works.

At this point, you should be able to connect from Enterprise Guide to Oracle by either writing an Oracle libname statement in the code editor, or using the "Tools | Assign Project Library" menu items.

From here, your Oracle environment should look like a SAS library, you can read and write Oracle tables from SAS.

The SAS/Access documentation is online, and it's pretty good. Start with the basic options, and then optimize as problems come up.

  Tom

Contributor
Posts: 27

Re: Introduction, EG + Oracle + recursive programming

Hi,

You can access an Oracle database via SAS/ACCESS Interface to ODBC, too.

ODBC driver for Oracle is needed, ODBC-connections have to be defined.

SAS server, Oracle Client, ODBC-driver must be the same flavor: 32-bit or 64-bit (and not mixed).

You might want to have a look on Oracle SQL Developer, too.

Here you can read about recursive programming:

Recursion (computer science) - Wikipedia, the free encyclopedia

Sándor

New Contributor
Posts: 4

Re: Introduction, EG + Oracle + recursive programming

Thanks Sandor,

Access to Oracle looks ok now.

And about recursive programming, the algorith is already ready. I just need to know how to make it work in SAS. Should I write the recursive code direct as a program (using "while" and another loops functions) or should I use EG as a workflow to structure the recursiveness?

Regards,

Marcos Oba

Community Manager
Posts: 2,697

Re: Introduction, EG + Oracle + recursive programming

For looping or recursive behavior, you'll probably need to write your own program in the SAS language.

If you have SAS/OR, there is a built-in MILP solver in PROC OPTMODEL:

http://support.sas.com/documentation/cdl/en/ormpug/59679/HTML/default/viewer.htm#milpsolver.htm

And I think SAS/IML offers some nonlinear optimization subroutines.  You might not need to reimplement an entire algorithm.  You can post that question to the forum, or forum.

Chris

New Contributor
Posts: 4

Re: Introduction, EG + Oracle + recursive programming

Thanks a lot TOM,

I talked with the TI guys here and there was a problem with user authorization. Now the tables are showing off and I can access then. I´ll take a look on online docs.

Another point: if I have only rights to read and write these Oracle tables, but I cannot create another tables, intermediate data should be worked in internal SAS datasets?

Regards,

Marcos Oba

Trusted Advisor
Posts: 1,059

Re: Introduction, EG + Oracle + recursive programming

Hi, Marcos

Yes, using SAS for intermediate tables should be fine. If your datasets contain record counts into the hundreds of thousands, there should be no problems at all. If the count of records that your handling goes into the millions, there could be cases where there would be a real time saving by doing your data reduction all in Oracle, which would involve creating Oracle datasets, but that's a specialized case which probably won't come up.

Good luck!

  Tom

New Contributor
Posts: 4

Re: Introduction, EG + Oracle + recursive programming

Hi, here I am again!

I want to use recursion to do something like follows:

1- slice the problem in small parts

2- optimize each small part with SAS OR

3- if the result matches some conditions, the answer is accepted, otherwise, this small part goes to a secondary list

4- after running all small parts in the first step, the eventual secundary list of small parts are put together and sliced again in small parts using a different criteria.

5- return to point 2, but this time the step 3 criterias are different

I need to run this loop 9 times, each time with different criterias.

The question is: I never worked with SAS in a recursive way, it was always a 1 shot run. How do I need to structure the loop? Macros?

Thanks a Lot!

Marcos

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 373 views
  • 3 likes
  • 4 in conversation