About OscarBoots2

OscarBoots2 · ‎03-07-2018

Hi, I'm using the code below & in this order. libname oralib1 oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =xxxxxxxxxxxx.com)(PORT =1234 )) (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))" user=xxxxxxxx pw=xxxxxxxxx connection=global dbmstemp=yes; /* This Query will only create an empty table */ proc sql; connect to oracle (user=xxxxxx pw=xxxxxx connection=global); execute (create global temporary table TEMP (ID number) on commit preserve rows) by oracle; quit; /* You defined your libname as oralib1 not ORA */ /* Insert records into temp table using Data Step */ data oralib1.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES); set SAS_DATASET; run; Can you advise the 'Load' statement & where it should go in the above statement? Thanks

OscarBoots2 · ‎03-07-2018

Hi SuryaKiran, I don't see any output or message after running this code? proc sql; select count(*) from oralib1.temp; quit; Also could you advise on an Insert Data Step that suits this script, I can't find anything useful as yet. Thanks

OscarBoots2 · ‎03-07-2018

Thanks Patrick, What I'll be doing with the initial Pass Through is to create a Temp table of only the matching IDs between the two tables (results approx 60K records). Then I want to bring this dataset in as a filter on IDs to get some other fields that I can't get in a reasonale amount of time from the large tables. So I'm almost at the stage of having my Temp table ready, I just need insert to work. Ideally I just need the correct way to insert & view my temp table records. Thanks

OscarBoots2 · ‎03-07-2018

Ok, I think I have it, could you give me a Data Step insert statement I could use for this? Thanks

OscarBoots2 · ‎03-07-2018

Thanks so to see my temp table results, I should use the below? libname oralib1 oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =xxxxxxxxxxxx.com)(PORT =1234 )) (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))" user=xxxxxxxx pw=xxxxxxxxx connection=global dbmstemp=yes; /* This Query will only create an empty table */ proc sql; connect to oracle (user=xxxxxx pw=xxxxxx connection=global); execute (create global temporary table TEMP (ID number) on commit preserve rows) by oracle; quit; /* Insert records into temp table using PROC APPEND */ proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET; run; data ora.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES); set SAS_DATASET = select distinct ID from mytable ; run;

OscarBoots2 · ‎03-07-2018

Thanks, should I be adding a select statement after 'data=' in order to get results?

OscarBoots2 · ‎03-07-2018

Thanks Suryakiran, That worked or at least didn't error, so I assume I now have created a temp table because there's no visible result. How can I now insert my records from a query into this table? Thanks

OscarBoots2 · ‎03-07-2018

Thanks to all for your input so far. Suryakiran, I've changed your code to do what I think I need to do to get the connection right, can you advise if it's correct or not. Note, I have HOST & PATH in my connection criteria, how do I adapt it to a Global Temp criteria. Thanks libname ora oracle user=xxxxxxxx pw=xxxxxxx path= ? (HOST = XXXXXXXXXXXXXXX.com) (PORT = 1234) connection=global DBMSTEMP=YES; proc sql; connect to oracle (user=XXXXXXXX pw=XXXXXXXX path= ? connection=global); execute (create global temporary table TEMP (ID number) on commit preserve rows) by oracle; quit; proc append base=ora.TEMP(Bulkload=YES) data=SAS_DATASET; run;

OscarBoots2 · ‎03-07-2018

Thanks Patrick, I have tried a subquery but it is still far to slow to help in this situation. Cheers

OscarBoots2 · ‎03-07-2018

Hi kiranv, Are you able to use my posted code to show me the correct Oracle syntax for this or suggest an article I could check? I'm having some trouble blending the two together. No worries if you're a Teradata user, I am too & I'm trying to get this right nit having used Teradata for a while. Thanks

OscarBoots2 · ‎03-07-2018

Thanks kiranv, That's the sort of solution I'm after. Let me give it a try. Thanks

OscarBoots2 · ‎03-07-2018

This may help explain my issue; I had a thought that if a temp table isn't an option, maybe an export to a new location may be an answer? Just thinking of any possibilities. proc sql; connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" user=xxxxxx pw=xxxxxxxx); create table WORK.xxxxxxxxx as select * from connection to ora ( select distinct A.ID from QUERY A LEFT JOIN QUERY_NEXT B ON A.ID = B.ID /*-----------------------------------------------*/ /*Is it possible to add code here to export the data back to another location?*/ /*--------------------------------------------------*/ ); disconnect from ora; quit;

OscarBoots2 · ‎03-07-2018

Hi Forum, Using SAS EG 7.1 with Windows 7. I have 2 large tables, one with 100,000s of records & one with millions of records that I access via an Oracle Pass Through query. To keep the time taken to run down, I've firstly created a Pass Through that extracts the common data between the 2 tables reducing the data to 60K uniq IDs. I now want to use that data as a join in a new Pass Through so I can get some more fields from these large tables. The plan is that the tables now only need to check 60K of records instead of the entire table. Unfortunately, a subquery still takes too long to run & when I try to add the extracted dataset in the Pass Through, it's not recognised I wonder if the 60K of Unique IDs can be saved somewhere (Temp table?) so It will be usable in the second Pass Through? Any suggestions welcome. Thanks

OscarBoots2 · ‎02-19-2018

Thanks SasKiwi, That's what I wanted to know! : ) Cheers

OscarBoots2 · ‎02-19-2018

And just to put this in context here's a link to instructions on creating these graphs; https://blogs.sas.com/content/graphicallyspeaking/2011/12/22/creating-graphs-can-be-a-multi-layered-piece-of-cake/

Online Status	Offline
Date Last Visited	‎04-06-2018 01:45 AM

Re: Join Dataset from SAS Work library to Oracle Pass Through

Re: Join Dataset from SAS Work library to Oracle Pass Through

Re: Reuse a table created using a Pass Through query

Re: Reuse a table created using a Pass Through query

Re: Libname syntax from connection string

Re: Libname syntax from connection string

Re: Libname syntax from connection string

Re: Reuse a table created using a Pass Through query

Re: Reuse a table created using a Pass Through query

Libname syntax from connection string

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Re: Use the results of a Pass Through query in another Pass Through qu...

Use the results of a Pass Through query in another Pass Through query

Re: Where to add code for Custom Graphs in SAS VA

Re: Where to add code for Custom Graphs in SAS VA