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

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

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

7 REPLIES 7
TomKari
Onyx | Level 15

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

SandorSzalma
Fluorite | Level 6

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

M_Oba
Calcite | Level 5

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
M_Oba
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

M_Oba
Calcite | Level 5

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

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
  • 1216 views
  • 4 likes
  • 4 in conversation