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?
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.
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;
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.