BookmarkSubscribeRSS Feed
Pradeepbanu
Obsidian | Level 7

I have created a SAS table like

Create table DX.SAS_Table as
select * from connection to oracle
(
select * from my table
)

Now I want to insert count of SAS_Table into another Oracle table, so I did this

Proc sql;
connect to oracle (connection..!)
execute(
INSERT INTO TEST_SAS_INSERT
select count(*) from SAS_Table
) by oracle;
quit;
It throwing me the error **ERROR: ORACLE execute error: ORA-00942: table or view does not exist**, I tried this Select count(*) from Dual its working but not from SAS_Table I understood this throw error Oracle considering SAS_table as oracle table, How can I do this?

6 REPLIES 6
LinusH
Tourmaline | Level 20

Don't use explicit SQL pass through, write your SQL targeting a libref pointing to your Oracle schema.

 

BTW: your first query doesn't benefit from explicit pass-through, you you use a libref in that case as well.

Data never sleeps
Sven111
Pyrite | Level 9

On the INSERT part Oracle is trying to reference the table SAS_Table as a local Oracle table, not the SAS table you created previously.

execute(
INSERT INTO TEST_SAS_INSERT
select count(*) from SAS_Table
) by oracle;

Like @LinusH mentioned, in this case the best way to handle it is via implicit SQL pass through rather than explicit.

 

 

s_lassen
Meteorite | Level 14

Why not just insert the number of observations previously selected, which is returned in the SQLOBS macro variable:

create table DX.SAS_Table as
select * from connection to oracle
(
select * from my table
);
execute by Oracle(
  insert into TEST_SAS_INSERT values(&sqlobs)
  );

If you want to insert data from SAS tables into Oracle tables, the easiest is normally to allocate the Oracle connection as a SAS library:

libname Oralib Oracle <connection options here>;

Proc sql;
  INSERT into Oralib.TEST_SAS_INSERT
  select count(*) from SAS_Table
 ;
quit;
JJP1
Pyrite | Level 9

Hi team,

I need to insert data for single column into SAS dataset using orcale table.please help

Patrick
Opal | Level 21

@JJP1 

Please don't piggyback on old discussions but ask a new question. Eventually post a link to an old discussion if it's relevant for what you're asking.

As the owner of the new question you will then also be able to select one of the answers as solution and though close the discussion.

 

What you're after doesn't appear to be hard but I'm not going to give an answer here.

JJP1
Pyrite | Level 9
Sorry @Patrick,Thanks for correction,I have created new one please

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 7590 views
  • 3 likes
  • 6 in conversation