在一个表中查找值并根据 IF 条件 (MERGE/SQL) 添加到数据集? [英] Lookup values in one table and add to dataset according to IF condition (MERGE/SQL)?

问题描述

我需要从一个表中查找数据并将其添加到基于 if 条件的主数据表中:数据是否被标记为缺失.假设查找表包含国家和港口.主文件中缺少需要填写的端口名称.仅当 flag = 1(它丢失)时，它才会使用查找来填充这些.

I need to lookup data from one table and add it to a master data table based on an if condition: whether the data is flagged as missing. Say the lookup table contains countries and ports. There are missing port names in the master file that need to be filled. It fills these using the lookup only if flag = 1 (it's missing).

此命令不起作用(不会将其填充 & 不会保留标志 =0 的 obs):

This command doesn't work (won't fill it in & won't keep the obs with Flag =0):

proc sql; 
create table data.varswprice1 as 
select * 
from data.varswprice a left join data.LPortsFill b
on a.LoadCountry = b.LoadCountry and a.LoadArea = b.LoadArea
where LPortMiss = 1;
quit;

这是一个包含一些数据的示例...

Here's an example with a bit of the data...

LOOKUP 表(3 个变量):

LOOKUP table (3 vars):

LoadPort LoadCountry  LoadArea
ARZEW    ALGERIA      NAF

MASTER(许多变量):

MASTER (many vars):

OBS    LoadPort  LoadCountry LoadArea  LPortMiss
1                 ALGERIA    NAF        1
2      ADELAIDE  AUSTRALIA   SEOZ       0

因此，基于 LPortMiss = 1 且 LoadCountry 和 LoadArea 相等的事实，它需要用 LOOKUP (ARZEW) 中的第一个 obs 填充 MASTER 中的第一个 obs.LOOKUP 和 MASTER 中还有更多 obs，但我希望这能更好地说明问题.

So, it needs to fill in the first obs in MASTER with the first obs in LOOKUP (ARZEW) based on the fact that LPortMiss = 1 and LoadCountry and LoadArea are equal. There are many more obs in LOOKUP and MASTER but I hope this illustrates the problem better.

推荐答案

你也可以在 proc sql 中使用 UPDATE 函数，这样就省去了创建新数据集的麻烦.您可能还想重置 lportmiss 标志.

You can also use the UPDATE function in proc sql, this saves having to create a new dataset. You would probably want to reset the lportmiss flag as well.

proc sql;
update master as a
    set loadport=(select loadport from lookup as b
        where a.LoadCountry=b.LoadCountry and a.LoadArea=b.LoadArea)
where lportmiss=1;
quit;

这篇关于在一个表中查找值并根据 IF 条件 (MERGE/SQL) 添加到数据集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！