Help using Base SAS procedures

Insert SAS table values into Oracle table

Reply
Occasional Contributor
Posts: 13

Insert SAS table values into Oracle table

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?

Super User
Posts: 5,876

Re: Insert SAS table values into Oracle table

Posted in reply to Pradeepbanu

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
Frequent Contributor
Posts: 99

Re: Insert SAS table values into Oracle table

Posted in reply to Pradeepbanu

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.

 

 

Highlighted
PROC Star
Posts: 260

Re: Insert SAS table values into Oracle table

Posted in reply to Pradeepbanu

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;
Ask a Question
Discussion stats
  • 3 replies
  • 174 views
  • 3 likes
  • 4 in conversation