像 Excel 的“VLOOKUP"一样工作的 SAS 代码;功能 [英] SAS Code that works like Excel's "VLOOKUP" function
问题描述
我正在寻找与 Excel 中的VLOOKUP"函数类似的 SAS 代码.
I'm looking for a SAS Code that works just like "VLOOKUP" function in Excel.
我有两张桌子:table_1 有一个 ID 列,其中包含 10 行的其他一些列.Table_2 有两列:ID 和定义,共 50 行.我想在 table_1 中定义一个新变量Definition"并从 table_2 中查找 ID 值.
I have two tables: table_1 has an ID column with some other columns in it with 10 rows. Table_2 has two columns: ID and Definition with 50 rows. I want to define a new variable "Definition " in table_1 and lookup the ID values from table_2.
除了合并之外,我还没有真正尝试过任何其他方法.但是合并保留了 table_2 中所有额外的 40 个变量,这不是我喜欢的.
I haven't really tried anything other than merge. but merge keeps all the extra 40 variables from table_2 and that's not what I like.
谢谢,SE
推荐答案
最简单的方法是在 merge
语句中使用 keep
选项.
The simplest way is to use the keep
option on your merge
statement.
data result;
merge table_1 (in=a) table_2 (in=b keep=id definition);
by id;
if a;
run;
另一种意味着您不必对数据集进行排序的方法是使用 proc sql.
An alternative that means you don't have to sort your datasets is to use proc sql.
proc sql;
create table result as
select a.*,
b.definition
from table_1 a
left join table_2 b on a.id = b.id;
quit;
最后,如果 table_2 很小,还有哈希表选项:
Finally, there is the hash table option if table_2 is small:
data result;
if _n_ = 1 then do;
declare hash b(dataset:'table_2');
b.definekey('id');
b.definedata('definition');
b.definedone();
call missing(definition);
end;
set table_1;
b.find();
run;
这篇关于像 Excel 的“VLOOKUP"一样工作的 SAS 代码;功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!