时间戳

首页 > 解决方案 > 从 SAS 中的单独表中查找和替换值

replace - 从 SAS 中的单独表中查找和替换值

问题描述

数据集HAVE包括两个名称拼写错误的变量：names和friends.

Name   Age   Friend
Jon     11   Ann
Jon     11   Tom
Jimb    12   Egg
Joe     11   Egg
Joe     11   Anne
Joe     11   Tom
Jed     10   Ann

我有一个小数据集CORRECTIONS，其中包括wrong_names和resolved_names。

current_names   resolved_names
Jon             John
Ann             Anne
Jimb            Jim

我需要 innames或friendsin与列中HAVE的名称匹配的任何名称，以便将其重新编码为. 生成的数据集应如下所示：wrong_namesCORRECTIONSresolved_nameWANT

Name   Age   Friend
John    11   Anne
John    11   Tom
Jim     12   Egg
Joe     11   Egg
Joe     11   Anne
Joe     11   Tom
Jed     10   Anne

在 R 中，我可以简单地使用 调用每个数据帧和向量if_else()，但是 SAS 中的 DATA 步骤不能很好地处理多个数据集。如何使用CORRECTIONS查找表进行这些替换？

标签： replacesasrecode

解决方案

有很多方法可以在 SAS 中进行查找。

但是，首先，我建议对您的查找表进行重复数据删除（例如，使用 PROC SORT 和 Data Step/Set/By）——决定保留哪个重复项（如果存在）。

至于查找任务本身，为了简单和学习，我建议如下：

“OLD SCHOOL”方式 - 适用于审计输入和输出（当输入表处于所需顺序时，更容易验证连接结果）：

*** data to validate;
data have;
length name $10. age 4. friend $10.;
input name age friend;
datalines;
Jon     11   Ann
Jon     11   Tom
Jimb    12   Egg
Joe     11   Egg
Joe     11   Anne
Joe     11   Tom
Jed     10   Ann
run;

*** lookup table;
data corrections;
length current_names $10.  resolved_names $10.;
input current_names   resolved_names;
datalines;
Jon             John
Ann             Anne
Jimb            Jim
run;

*** de-duplicate lookup table;
proc sort data=corrections nodupkey; by current_names; run;

proc sort data=have; by name; run;   

data have_corrected;
    merge have(in=a) 
          corrections(in=b rename=(current_names=name))
          ;
    by name;
    if a;
    if b then do;
        name=resolved_names;
    end;
run;

SQL 方式 - 避免对 have 表进行排序：

proc sql;
    create table have_corrected_sql as
    select 
        coalesce(b.resolved_names, a.name) as name, 
        a.age, 
        a.friend
    from work.have as a left join work.corrections as b
    on a.name eq b.current_names
    order by name;
quit;

注意 Coalesce() 用于将缺少的 resolved_names 值（即没有更正时）替换为 have 表中的名称

编辑：为了反映昆汀（正确）的评论，我错过了对姓名和朋友字段的更新。

基于更正这两个字段，还有许多方法，但本质是仅在查找（更正）表中存在值时才更新值。一旦你理解了它的声明，散列对象就非常擅长这一点。

注意：Hash 对象中的任何关键字段都需要在 Length 语句之前指定。

编辑：根据 ChrisJ 对 Length 语句声明的替代方法和我的回复（见下文） - 最好在声明哈希表之前声明关键变量需要定义。

data have_corrected;
keep name age friend;
length current_names $10.;

    *** load valid names into hash lookup table;
    if _n_=1 then do;
        declare hash h(dataset: 'work.corrections');
        rc = h.defineKey('current_names');
        rc = h.defineData('resolved_names');
        rc = h.defineDone();
    end;
    do until(eof);
        set have(in=a) end=eof;
        *** validate both name fields;  
        if h.find(key:name) eq 0 then
            name = resolved_names;
        if h.find(key:friend) eq 0 then
            friend = resolved_names;
        output;
    end;
run;

编辑：回答关于 ChrisJ 的 SQL/Update 替代方案的评论

基本上，您需要将每个 UPDATE 语句限制为仅在更正表中具有名称值或朋友值的那些行 - 这是通过在您指定 set var =（子句）之后添加另一个 where 子句来完成的。见下文。

注意。AFAIK，满足您要求的 SQL 解决方案将需要超过 1 遍基表和查找表。

然而，查找/哈希表需要基表的单次传递、查找表的加载以及查找操作本身。您可以在日志中看到性能差异...

proc sql;
*** create copy of have table;
create table work.have_sql as select * from work.have;
*** correct name field;
update work.have_sql as u
    set name = (select resolved_names 
                from work.corrections as n
                where u.name=n.current_names)
    where u.name in (select current_names from work.corrections)
        ;
*** correct friend field;
update work.have_sql as u
    set friend = (select resolved_names 
                  from work.corrections as n
                  where u.friend=n.current_names)
    where u.friend in (select current_names from work.corrections)
        ;
quit;

推荐阅读