03-06-2018 02:37 AM
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
connect to oracle (connection..!)
INSERT INTO TEST_SAS_INSERT
select count(*) from SAS_Table
) by oracle;
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?
03-06-2018 09:10 AM
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.
03-06-2018 11:33 AM
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.
03-08-2018 05:52 AM
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;