在sas中合并sql的类似语句 [英] similar statement to merge sql in sas

问题描述

我需要在Sas中合并两个表

hi guys i need to merge two tables in Sas

但条件类似于sql merge语句中

but with a condition like in a sql merge statement:

MERGE INTO TABLE_NAME USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

有这样的东西吗?

如果没有，你能告诉我如何进行附条件的这种追加吗?

in case there isn't can you tell me how can i make this kind of append with condition ?

推荐答案

我不知道这样做的任何直接方法.在过去完成此操作后，我首先添加了新行(使用proc append)，然后又进行了第二步以添加modify现有行.对于修改步骤，如果要就地重建表，则需要在单个数据步骤中结合使用数据步骤和set和modify语句.这样做的好处是它的I/O少得多(如果它是一个大表，并且只有一个小的子集可以更新)，并且保留现有索引.缺点是它要复杂得多.代码看起来像这样:

I'm not aware of any straightforward way of doing this. When I have done it in the past I first appended new rows (using proc append) and then had a second step to modify existing rows. For the modify step you need to use a combination of a datastep with a set and a modify statement in a single datastep if you want to rebuild the table in-place. The advantage of doing this is that it's a lot less I/O (if it's a big table and only a small subset to update) and it keeps existing indexes. The downside is it's a lot more complicated. The code looks something like this:

  **
  ** REPLACE VALUES IN CPANEL.PW_STAT WITHOUT REBUILDING THE ENTIRE TABLE.
  *;
  data cpanel.pw_stat;

    set redirect_updates;
    modify cpanel.pw_stat key=primary;

    select(_iorc_);  
      when(%sysrc(_sok)) do;
        * MATCHED TRANSACTION DATASET TO MASTER DATASET. REPLACE THE VALUE;
        if date_redirected ne tmp_date_redirected then do;
          date_redirected = tmp_date_redirected;
          replace;
        end;
      end;
      when(%sysrc(_dsenom)) do;
        * NO MATCH. DO NOT ADD OBS OR REPLACE OBS OR DELETE OBS.  RESET ERR AND DO NOTHING.;
        _error_ = 0;
      end;
      otherwise do;
        put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
        put 'Program terminating. DATA step iteration # ' _n_;
        put _all_;
        stop;
      end;
    end;
  run;

我应该补充一点，如果性能不是问题，那么从头开始重新创建表会更好.

I should add that if performance isn't an issue you are much better off just re-creating a table from scratch.

这篇关于在sas中合并sql的类似语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！