Prepare SAS data to populate SQL Server temporary table, use that temp table to join with base sql server table in SAS EG

Question

I have a scenario where I need to select data from a Teradata table in a database A. Use that result set to populate a SQL server temp table. This temp table needs to join with base SQL server table . The result set of this needs to be exported to another database table. Need to do it in SAS enterprise guide

This is my approach so far, First create a SAS data set

STEP 1

proc sql;
  connect to teradata(user="abc" pw="def" database=cust fast load=yes mode=Teradata);
create table tmp_result as
select  from connection to teradata
(
SELECT 
Cust_id,
Name,
Product
 FROM Teradata table where ProductId=10
)DISCONNECT FROM TERADATA;
QUIT;

STEP 2

proc sql;
connect to odbc(dsn=temp user="abc" pw="def" connection=shared);
create table ##tmp
(
Cust_id int
Name varchar(100),
Product varchar(50);
)disconnect from ODBC;
QUIT;

STEP 3--insert data into temp table from first SAS data set

step 4 -create a base sql server table out of the below select query

SELECT *
FROM base sql server table join temp table (above temp table)

step 5 again create a sas data set out of above step 4 result set and then in final step

step 6 insert step 5 result set into a teradata table database B.

I need help with my approach..I am still working on this solution but need your input if my approach is correct

Thanks in advance..

Kiran Kiran · Accepted Answer · 2018-12-17T11:47:41

You can do an implicit sql pass through by defining libname for your databases.

     libname teralib teradata server=myserver user=myuserid pwd=mypass;
     libname sqllib sqlsvr user=testuser password=testpass;
     libname saslib "somelocation"; /* you do not need this unless your manipulating 
   something in SAS*/

 proc sql;
 create teralib.yourotherteradatatable as
select a.id, b.col from sqllib.yoursqltable a
inner join teralib.yourteradatatable b
on a.yourinterstedcol = b.yourinterstedcol;
quit;

Prepare SAS data to populate SQL Server temporary table, use that temp table to join with base sql server table in SAS EG

2 Answers