sql - 根据条件将正确的值作为一个变量提取

Question

基本上我要做的是说：如果键上有匹配项，则根据日期将正确的（新）值插入到变量中。如果没有匹配，那么我想要旧表中的值以下是我的示例代码：

    proc sql;
create table fly.Formulary2017
as select
d.corp_ent_cd
,d.groupno
,case when p.EffectiveDate > d.cvmo
        then d.old
when p.EffectiveDate <= d.cvmo
        then p.new
else d.old
        end as WANT
,d.Key1
from lib.dsl d FULL JOIN lib.post p
on d.Key1=p.Key1
;   
quit;

所以不一定每个键都匹配。对于那些没有匹配项的我想要旧值。

标签： sqljoinsasouter-joincase-when

Answer 1

您需要在代码中包含匹配记录逻辑，如下所示。这是下面的小示例代码。我已经分配了“新”和“旧”，而不是你可以有列。

 /***partial code.***/

case when d.Key1 = p.Key1  and p.EffectiveDate > d.cvmo
    then "new"
when d.Key1 ne p.Key1
 then "old"
  when d.Key1 = p.Key1 and  p.EffectiveDate <= d.cvmo
    then "old"


 /*full code to try*/

 data post;
input key1 EffectiveDate:date9. ;
 format EffectiveDate date9.;
datalines;
10 10OCT2018 
11 22OCT2018 
12 27OCT2018
15 10NOV2018
16 22NOV2018
 17 27NOV2018
;

data dsl;
input key1 cvmo:date9. ;
format cvmo date9.;
datalines;
10 17OCT2018 
11 1OCT2018 
16 22NOV2018 
17 27NOV2018 
;

proc sql;
select
p.key1,
, case when d.Key1 = p.Key1  and p.EffectiveDate > d.cvmo
    then "new"
when d.Key1 ne p.Key1
 then "old"
  when d.Key1 = p.Key1 and  p.EffectiveDate <= d.cvmo
    then "old"
    end as WANT

from dsl d FULL JOIN post p
on d.Key1=p.Key1
 ;   
quit;