topic Re: Insert SAS table values into Oracle table in SAS Procedures

Insert SAS table values into Oracle table

Pradeepbanu — Tue, 06 Mar 2018 07:37:55 GMT

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?

Re: Insert SAS table values into Oracle table

LinusH — Tue, 06 Mar 2018 14:10:24 GMT

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.

Re: Insert SAS table values into Oracle table

Sven111 — Tue, 06 Mar 2018 16:33:45 GMT

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.

Re: Insert SAS table values into Oracle table

s_lassen — Thu, 08 Mar 2018 10:52:20 GMT

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;

Re: Insert into SAS table using oracle table

JJP1 — Fri, 13 Sep 2019 14:08:37 GMT

Hi team,

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

Re: Insert into SAS table using oracle table

Patrick — Sun, 15 Sep 2019 00:43:43 GMT

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

Re: Insert into SAS table using oracle table

JJP1 — Mon, 16 Sep 2019 06:14:35 GMT

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