PROSAGA码农传奇-SAS-如何使用另一个表的数据替换表中的数据，类似于Excel中的vlookup？

0# 晴天？霹雳 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  <P>
    SAS具有独特的功能
    的<strong>
      自定义格式
    </强>
    。格式将源值映射到目标值，这与VLOOKUP非常相似。
  </p>
  <P>
    格式通过使用与变量相关联
     <code>
      FORMAT
    </code>
     声明。
  </p>
   <pre>
    <code>
      proc format;
  value $MyFormat
  'A' = 'AA'
  'B' = 'BB'
  'C' = 'CC'
  ;
run;

data have;
  input col1 $ @@; 
  col1_formatted_value = put(col1,$MyFormat.); * typically don't have to do this;
  datalines;
  A A A B B C C D D A
run;

proc print data=have;
  title "Data rendered per attributes associated with variables in data set metadata";
run;
proc print data=have;
  title "col1 Format applied at step time";
  format col1 $MyFormat.;
run;

* col1 format attribute saved with data set;
data have2;
  input col1 $ @@; 
  format col1 $MyFormat.;
  datalines;
  A A A B B C C D D A
run;

proc print data=have2;
  title "Data rendered per format attributes associated with variables (in data set metadata)";
run;

</code>
  </pre>
  <P>
    SAS格式也可以直接从数据构建：
  </p>
   <pre>
    <code>
      data formatMappingData;
input source $ target $;
fmtname = "$MyFormatFromData";
start = source;
label = target;
datalines;
A AA!
B BB!
C CC!
;
run;

proc format cntlin=formatMappingData;
run;

proc print data=have2;
  title "Data rendered per format attributes associated with variables (in data set metadata)";
  format col1 $MyFormatFromData.;
run;

</code>
  </pre>
</DIV>

1# 晴天 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”>
  <P>
    我找到了解决方案！
  </p>
  <P>
    谢谢大家，所有答案，他们给了我一些见解。
  </p>
  <P>
    @nvioli和@DCR给了我很大的见解。我正在努力了解我正在生成的笛卡儿产品。我对行进行了计数，并在结果中找到了与原始t1表相比相同数量的行。但总结的价值显然是错误的。所以我理解，不知何故，我的代码是在每行中插入总和而不是“group by”的小计。
  </p>
  <P>
    我用最简单的解决方案解决了这个问题：我在两个不同的视图中拆分了视图。第一个是分组和求和，因为这段代码的旧版本正确地执行了。第二个视图只会在简单选择中保留连接和更改数据。最终代码是这样的（简化版本，作为原始示例）：
  </p>
   <pre>
    <code>
      /*view to group and sum columns from t1*/
proc sql;
    create view 
        v1 as
    select
        t1.c1, (column that will be substitute later)
        t1.c10, (not relevant to problem, only to show the "criteria"/group by)
        SUM(t1.c11) (not relevant to problem, only to show sum)
    from 
        _outres.table1 t1
    where
        t1.c10= "criteria"
    group by
        t1.c1,
        t1.c10
    ;quit;run;

</code>
  </pre>
  <P>
    之后：
  </p>
   <pre>
    <code>
      /*view to substitute the desired column from t1 (now v1) */
proc sql;
    create view 
        v2 as
select
        t2.c2, (column with new data)
        t1.c10, (now already grouped)
        Sum_of_t1.c11 (now already summed)
from 
    v1 
left join
    t2
on
    v1.c1 = t2.c1 (comparing view from t1 with t2)
;quit;run;

</code>
  </pre>
</DIV>

2# Jacob | 2019-08-31 10-32