54289_en-US

liliconcom

Add new connection to to an exising jmpquer

Using SAS since late 80's.

Just converted SAS 9.1.to JMP 13.1

 

Had 50+ libraries in SAS - Oracle, SQL Server, MSAccess.

If I had a SAS library connected to the Oracle medical data, and needed to 

connect to the historical data stored in another Oracle schema, simply ran a script

where medical.table1 sysid = cenusdata.sysid.

Have ported over many of my thousand+ SAS scripts.

Now I'm stuck.

 

If a script is pulling from the JMP Oracle medical data, and I want to 

connect to the census data in another Oracle schema 

IT CAN'T BE DONE.

 

There is no way to bring in a table from a different Oracle (or MSaccess or Excel, etc.) connection.  

I DON'T WANT TO ADD ANOTHER TABLE FROM THE CURRENT CONNECTION, 

I NEED TO BRING IN A TABLE FROM DIFFERENT CONNECTION AND JOIN THEM USING THE QUERY BUILDER.

 

Can't believe it's this hard - something that takes, (create a library in SAS 9.1), 30 seconds to do in SAS 9.1

Re: Add new connection to to an existing jmpquery

Thanks again Eric.

We are stuck with JMP, no alternative, SAS 9.1 was on an XP box, not secure and was taken down.

 

Is there a white paper/web link how to set up a SAS server?.

I do see the Medata SAS Server Connection data connection option, and was thinking that’s probably the best way to combine all my ORACLE/SQLserver connections

into one-stop shopping, so to speak.  Our IT staff is severely callenged, so it would have to 

be mostly self-explanitory, but we have tons of server storage.

 

Wouldn't it just have been easier to place an additional button in the Query builder next to 

"Change Data source" - "ADD Data Source(s)".

No one in the beta testing group thought of that?  I can't be the only SAS user who combined data from multiple libraries.....

 

 

Re: Add new connection to to an existing jmpquery

You could use Oracle database links to connect to a different database in a single query.  For example:

SELECT t1.field1, t1.field2, t2.field3, t2.field4
  FROM myschema.datatable t1,
       otherschema.othertable@database_link t2
 WHERE t1.id = t2.id

Re: Add new connection to to an existing jmpquery

What is the reasoning for involving JMP in this analysis process?  Given the data size and the privacy concerns, it may be that JMP is just not the right tool for what you're doing.

But I'll throw out one other possibility:  You can write SAS programs in JMP and submit them to your SAS server, if your SAS server has Integration Technologies.  If you could do that so that SAS is doing the queries and getting the data down to a more manageable size (and leaving out personally identifying information), and just bring the final result down to JMP to analyze, maybe that would be a better division of labor for the task you are trying to accomplish.

HTH,

Eric

 

 

 

Re: Add new connection to to an existing jmpquery

Eric,

 

Thanks for  your prompt reply.

 

Um, keeping 10 million records,50 columns each on an unsecured server containing SSN. DOB, criminalogic history is a no go, space-wise and security-wise. 

Beside, the IT staff already  pays Oracle hundred of thousands of dollars to store our data to query it.

 

I'm not afraid of creating a scripted connection to the Oracle tables, and I will do it, just seems like a step backword.

 

Just sayin'

Re: Add new connection to to an exising jmpquer

You could come pretty close, but you'd have to use scripting.  A query that you build using Query Builder can be saved as a script.  The beginning of that script will look like this:

New SQL Query(
 Connection( "ODBC:DSN=mydsn" ),
 Select(),
 From(
  Table(
   "my_table",

where the DSN for your Oracle connection comes after the "ODBC:" in the Connection() argument.  If you stored your various Oracle connection strings in JSL variables, and replaced the argument to Connection() with a JSL variable containing the connection string you wanted to use, you should be able to accomplish this.

If you want to pull in tables from several different Oracle connections and then join those, you would have to pull them in separately into JMP data tables and then use JMP Query Builder (on the Tables menu) to join them.

JMP is not nearly as sophisticated as SAS when it comes to getting data out of databases, but with a little scripting work, I think you can do what you've asked here.

HTH,

Eric