proc sql;
connect to db2 (user='423423' password='fwewerwr' database='sdfsdf' host='fwerwerwe');
create table table_abc (compress=yes) as select* from connection to db2 (
select *
from table2
where status ='A');
quit;
-- Executing from SAS 9.4 for windows
ERROR: The DB2 engine cannot be found.
ERROR: A Connection to the db2 DBMS is not currently supported, or is not installed at your
site.
Whenever I need to do SQL Pass-Through I use this:
PROC SQL;
CONNECT TO odbc ("dsn={name here}");
CREATE TABLE {Table Name} AS
SELECT * FROM connection to odbc
(
SELECT {Column}
FROM Table
);
DISCONNECT FROM odbc;
QUIT;
I personally don't have access to use the db2 connection method but I believe you would need to do it like this:
proc sql;
connect to db2 (user=_______ database=_______ password=_______);
execute( CREATE TABLE {Table Name} AS
SELECT * FROM connection to odbc
(SELECT {Column}
FROM Table) by db2;
disconnect from db2;
quit;
DSN is something you have pre-configured.
-- This would be at the top of your code
libname rptLib odbc dsn=rpt_Lib_name schema=dbo spool=NO;
--You would then reference it like this.
PROC SQL;
CREATE TABLE Test AS
SELECT *
FROM rptLib.Table
;QUIT;
If you have this then you can use the first snippet. If not then you'll need to configure the connection with DB2 which would require UserName, Password and Database.
you might need to have the DB2 client installed on your work-station
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: