Insert SAS table values into Oracle table

[Pradeepbanu]

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?

[LinusH]

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.

[Sven111]

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]

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]

Hi team,

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

[Patrick]

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

Sorry @Patrick,Thanks for correction,I have created new one please