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

[SuryaKiran]

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. 

[OscarBoots2]

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;

[SuryaKiran]

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;

[OscarBoots2]

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

Thanks

[Patrick]

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

[OscarBoots2]

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

[Patrick]

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

[OscarBoots2]

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 

[SuryaKiran]

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.

[OscarBoots2]

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 

[SuryaKiran]

Run this entire code and post your log info.

[OscarBoots2]

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

[SuryaKiran]

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

[OscarBoots2]

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. 

[Patrick]

@OscarBoots2

I believe it would be easier for you using the DBMASTER dataset option.

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