sql-server - Prepare SAS data to populate SQL Server temporary table, use that temp table to join with base sql server table in SAS EG
问题描述
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..
解决方案
您可以通过为数据库定义 libname 来执行隐式 sql 传递。
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;
推荐阅读
- c# - C# WebApi - Asp Net Core 2 - 如何以编程方式将 csproj 名称放入路由中?
- c# - 将一个属性名称与多个采用不同类型的构造函数一起使用
- animation - React Native LayoutAnimation scrollView 容器大小
- solr - 如何解决未定义字段文本错误?
- javascript - 在使用嵌套值之前检查是否有任何父对象未定义?
- android - Gradle 混合版本 27.1.1 和 26.1.0
- rust - 通过全局变量间接将引用作为参数传递
- ssis - SSIS 数据流任务 - 尝试将存储过程作为 OL DB SQL 命令的一部分执行
- python - 注意使用 NLTK 和 Wordnet 的程序不起作用,错误消息说是因为 wordnet
- javascript - “必需/输入电子邮件应包含'@'和至少一个点。'.'”的 PHP 代码