Add new connection to to an exising jmpquer

Question

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

MeatPopsicle · Answer

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

pmroz · Answer

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

Eric_Hill · Answer

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