使用SAS或Oracle SQL比较和插入

发布于 2025-01-21 08:46:30 字数 1990 浏览 0 评论 0原文

我使用隐式通过连接从Oracle表中查询的数据创建了两个表格库中的两个表。现在，我希望将表_x的Cola中的值与表_y中的colp中的值进行比较，如果它们不相等，则将cola插入colb，colb的colb值将colp的colp和colq值在table_y中插入。 Table_y的COLR值需要通过从SQL查询中获取其值来更新。示例数据

table_x如下所示

COLA	COLB
DAVE	01-APR-2022
MARK	05-JAN-2021
ROCK	10-FEB-2022

TABLE_Y table_y如下

Colp	Colp Colq	Colq Colq
Dave	01-APR-2022	100

我本质上想要的是 if value.table_x.cola！= value.table_y.colp 然后分别将表_x.cola和table_x.colb的值分别插入table_y.colp和table_y.colq。 table_y.colr值将由SQL查询填充（我可以使用隐式或明确的传递连接连接到SQL DBS）

输出应该看起来像该

COLP	COLP COLQ	COLQ DAVE
DAVE	01-APR-2022	100
的	最终 -2021	200
Rock	10-FEB-2022	500

我该如何在SAS和/或Oracle SQL中执行此操作（我可以使用explacit-pass通过和使用Oracle语法在Oracle中推出table_x和table_y。

原文

I have created two tables in SAS WORK Library using data queried from Oracle tables using implicit pass through connections. I am now looking to compare the values in colA of table_X with values in colP in table_Y and if they are not equal then insert the colA,colB values of table_X to colP and colQ values in table_Y. The colR value of table_Y needs to be updated by getting its value from a SQL Query.
Sample data

Table_X is as follows

colA	colB
DAVE	01-Apr-2022
MARK	05-Jan-2021
ROCK	10-FEB-2022

Table_Y is as follows

colP	colQ	colR
DAVE	01-Apr-2022	100

What I essentially want is
If Value.Table_X.colA != Value.Table_Y.colP
then INSERT that value of Table_X.colA and Table_X.colB INTO Table_Y.colP and Table_Y.colQ respectively. Table_Y.colR value would be filled by a SQL Query (which I can connect to SQL DBs using Implicit or Explicit Pass-Through connections)

Final Output for Table_Y should look like this

colP	colQ	colR
DAVE	01-Apr-2022	100
MARK	05-Jan-2021	200
ROCK	10-FEB-2022	500

How can I do this in SAS and/or Oracle SQL (I can push Table_X and Table_Y in Oracle using Explicit-Pass Through and use Oracle Syntax).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聚集的泪 2025-01-28 08:46:30

让我们将名称用作关键变量，将x和y用作数据集名称，以减少混淆。

听起来您只想获取X中的名称值列表，但在Y中却不是很容易的SQL。

proc sql;
  create table new as 
    select distinct name from X
    except
    select name from Y
 ;
quit;

或者，如果X和Y已经按名称进行排序，则可以做一个简单的数据步骤。

data new;
  merge x(in=inx) y(in=iny);
  by name;
  if inx and not iny and firt.name then output;
  keep name;
run;

一旦获得该列表，您可以将其上传到远程数据库中，并使用它来驱动查询以选择这些新观察值以附加到Y。或者如果列表足够小，则可以使用数据集来生成一个宏变量，您可以用于生成远程查询中的Where子句以选择这些值。

下载新数据后，您可以使用PROC Append将其添加到现有的Y数据集中。因此，如果将您的新观察结果下载到名为Newy的数据集中，那么您可以按这样的步骤运行：

proc append base=Y data=NEWY;
run;

Let's just use NAME as your key variable and X and Y as the dataset names to reduce confusion.

Sounds like you just want to get the list of values of NAME that are in X but not in Y. Easy enough in any flavor of SQL.

proc sql;
  create table new as 
    select distinct name from X
    except
    select name from Y
 ;
quit;

Or if X and Y are already sorted by NAME then a simple data step will do.

data new;
  merge x(in=inx) y(in=iny);
  by name;
  if inx and not iny and firt.name then output;
  keep name;
run;

Once you have that list you could upload it into your remote database and use it to drive the query to select those new observations to append to Y. Or if the list is small enough you could use the dataset to generate a macro variable that you could use to generate a WHERE clause in your remote query to just select those values.

Once you have downloaded the new data you could use PROC APPEND to add it to your existing Y dataset. So if your new observations were downloaded into a dataset named NEWY then you could run a step like this:

proc append base=Y data=NEWY;
run;

回复收藏 0 原文

等数载，海棠开 2025-01-28 08:46:30

假设colr位于表其他_table，也包含colp，您可以将整个table_x移动到目标平台。

然后运行此脚本（我将数据添加到CTE-S中以测试功能，您不需要条款）：

INSERT INTO table_y
-- your input, don't use in query ...
WITH
table_x(colA,colB) AS (
          SELECT 'DAVE',DATE '01-Apr-2022'
UNION ALL SELECT 'MARK',DATE '05-Jan-2021'
UNION ALL SELECT 'ROCK',DATE '10-FEB-2022'
)
,
other_table(colA,colR) AS (
          SELECT 'DAVE', 10
UNION ALL SELECT 'MARK',100
UNION ALL SELECT 'ROCK',500
)
-- end of necessary input, real rest of INSERT statemen starts here
SELECT
  table_x.colA
, table_x.colB
, other_table.colR
FROM table_x
JOIN other_table USING(colA)
LEFT JOIN table_y ON table_x.colA = table_y.colP
WHERE table_y.colP IS NULL;

控制查询：

SELECT * FROM table_y;
-- out  colP |    colQ    | colR 
-- out ------+------------+------
-- out  DAVE | 2022-04-01 |  100
-- out  MARK | 2021-01-05 |  100
-- out  ROCK | 2022-02-10 |  500

Assuming that colR sits in a table other_table, also containing colP, you could move the whole of table_x to the target platform.

Then run this script (I add the data in CTE-s to test the functionality, you don't need the WITH clause) :

INSERT INTO table_y
-- your input, don't use in query ...
WITH
table_x(colA,colB) AS (
          SELECT 'DAVE',DATE '01-Apr-2022'
UNION ALL SELECT 'MARK',DATE '05-Jan-2021'
UNION ALL SELECT 'ROCK',DATE '10-FEB-2022'
)
,
other_table(colA,colR) AS (
          SELECT 'DAVE', 10
UNION ALL SELECT 'MARK',100
UNION ALL SELECT 'ROCK',500
)
-- end of necessary input, real rest of INSERT statemen starts here
SELECT
  table_x.colA
, table_x.colB
, other_table.colR
FROM table_x
JOIN other_table USING(colA)
LEFT JOIN table_y ON table_x.colA = table_y.colP
WHERE table_y.colP IS NULL;

control query:

SELECT * FROM table_y;
-- out  colP |    colQ    | colR 
-- out ------+------------+------
-- out  DAVE | 2022-04-01 |  100
-- out  MARK | 2021-01-05 |  100
-- out  ROCK | 2022-02-10 |  500

回复收藏 0 原文

~没有更多了~