BookmarkSubscribeRSS Feed
SuryaKiran
Meteorite | Level 14

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
OscarBoots2
Calcite | Level 5

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
Meteorite | Level 14

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
OscarBoots2
Calcite | Level 5

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

 

Thanks

Patrick
Opal | Level 21

@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
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
Meteorite | Level 14
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
OscarBoots2
Calcite | Level 5

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
Meteorite | Level 14

Run this entire code and post your log info.

Thanks,
Suryakiran
OscarBoots2
Calcite | Level 5

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

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
OscarBoots2
Calcite | Level 5

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 34 replies
  • 3190 views
  • 2 likes
  • 4 in conversation