SAS supports ODBC, OLEDB ... different data sources but need to purchase the driver separately. Normally SAS only comes with ODBC.
1. Create a table from a query through ODBC.
proc sql;
connect to odbc(dsn='aubridebiw02');
create table work.test as
select * from connection to odbc(
select * from STG_MIDAS_STD_CODES
);
run;
To use ODBC on the fly
proc sql;
connect to odbc as myodbc (noprompt = "server=aubridebiw02;DRIVER=SQL Server;Trusted Connection=yes");
create table work.test as
select *
from connection to myodbc(
select * from STG_MIDAS_STD_CODES
);
Run;
2. Create a table from a query joining two tables
proc sql;
create table work.test as
select * from work.test1, work.test2
where test1_id = test2_id;
run;
3. Create a table from a query executing stored procedure
proc sql;
connect to odbc(dsn='aubridebiw02');
create table work.test as
select * from connection to odbc(
execute databasename.dbo.GET_NAME @CODE= 'ABC';
);
run;
4. To insert data from SAS back to a SQL table:
libname myodbc odbc noprompt = "server=192.168.1.1;DRIVER=SQL Server;Database=my_database;Trusted Connection=yes";
proc sql;
insert into myodbc.test_table (name, text)
select name, textfrom WORK.sas_table
quit;
note:The default schema is dbo. To specify a different schema, syntax is as following. The schema clause is outside of the quotations!
libname mylib odbc noprompt = "dsn=aubriprbiw02;Database=ANALYTICS;" schema=travel;