Desktop productivity for business analysts and programmers

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

Reply
Valued Guide
Posts: 597

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

Posted in reply to OscarBoots2

There is no such select statement for PROC APPEND. There are many ways to load data into Temp table one method is PROC APPEND. If you want to see the table then use the data set  or run select query from PROC SQL.

 

data ora.temp(BULKLOAD=YES BL_DELETE_DATAFILE=YES);

set <sasdataset>;

run;

 

You need BL_DELETE_DATAFILE=YES to delete .dat, .ctl and .log files which are by default created when you use BULKLOAD. 

Thanks,
Suryakiran
Contributor
Posts: 60

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

Posted in reply to SuryaKiran

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;



Valued Guide
Posts: 597

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

Posted in reply to OscarBoots2

If your using SAS EG then if you Load data using Data step then the result will be in output window.

 

 

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;

Or just run this to confirm the records loaded.

 

 

proc sql;

select count(*) from oralib1.temp;

quit;

 

Thanks,
Suryakiran
Contributor
Posts: 60

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

Posted in reply to SuryaKiran

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

 

Thanks

Respected Advisor
Posts: 4,736

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

Posted in reply to OscarBoots2

@OscarBoots2

If your remaining problem is to select rows in a large Oracle table based on keys/ids in a small SAS table then consider also the DBMASTER dataset option.

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n0jg0sozl17mjyn1woelrrr76266.htm&docs...

 

Contributor
Posts: 60

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

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

 

 

 

 

Respected Advisor
Posts: 4,736

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

Posted in reply to OscarBoots2

@OscarBoots2

I still don't understand why you need a temp table in first place - but then I don't have the full picture of your process.

If using an Oracle temp table just be aware that this is not the same like a SAS Work table. Once created the temp table structure is actually permanent, it's only the data attached to the structure which is temporary.

What gets people sometimes confused is the scope of this data - and if not having the right options set for the connections (I always have to look it up, global... shared) then you might Proc Append your data (that's how you load from SAS) to the temporary table but then in the next query this data is no more around but you've got only the permanent temp table structure with zero rows.. 

Contributor
Posts: 60

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

Posted in reply to SuryaKiran

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 

Valued Guide
Posts: 597

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

Posted in reply to OscarBoots2
Did you load the data and then run the query. What did your log say when
you loaded data. Also make sure your in the same session and have ON COMMIT
PRESERVE ROWS.
Thanks,
Suryakiran
Contributor
Posts: 60

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

Posted in reply to SuryaKiran

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 

Valued Guide
Posts: 597

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

Posted in reply to OscarBoots2

Run this entire code and post your log info.

Thanks,
Suryakiran
Contributor
Posts: 60

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

Posted in reply to SuryaKiran

I've run the code & there is no error or Log info.

Valued Guide
Posts: 597

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

Posted in reply to OscarBoots2

Strange!, why don't you get log info. Do you get log for other programs you run. 

Thanks,
Suryakiran
Contributor
Posts: 60

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

Posted in reply to SuryaKiran

I changed the server & it now gives me a log error;

 

ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.

 

I can fix this if you think the rest of the script is correct.

 

I'm wondering how the script populates the table as I would think it would need a select statement of some table. 

Respected Advisor
Posts: 4,736

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

Posted in reply to OscarBoots2
Ask a Question
Discussion stats
  • 34 replies
  • 287 views
  • 2 likes
  • 4 in conversation