modifying specific value, which has condition in another dataset.

[km0927]

hello. i want to modify specific data, which doesn't have 'common key' in other dataset. in dataset 1, there are several '9' in YN column. if YN is 9 and doesn't have same key in dataset 2, it changes to '0'

dataset 1 is here.

Have 1
id key YN
1 20010305 0
1 20010408 9
1 20010510 9
1 20010511 0
2 20030309 9
2 20030408 9
2 20030607 9
2 20030715 0
3 20040913 9
3 20041011 0
3 20050205 0
3 20060305 9

and dataset 2 is here.

key
20010408
20030309
20040913

i want to make dataset 1 like this.

id key YN
1 20010305 0
1 20010408 9
1 20010510 0
1 20010511 0
2 20030309 9
2 20030408 0
2 20030607 0
2 20030715 0
3 20040913 9
3 20041011 0
3 20050205 0
3 20060305 0

i used 'proc sql', but the results is strange. there are 12 rows in dataset 1, but after trying proc sql, the rows are several times multiplied. i cant' find what was wrong.

proc sql;
create table want as
select a.*,
case when a.RN_KEY=b.RN_KEY then 9
else 0 end as YN1
from have 1 as a, (select RN_KEY from have 2) as b
drop YN
;quit;

in addition, i don't want to make additional column 'YN1'. I want to modify exisiting variable and don't want to use 'drop YN' statement. is there another way to preserve existing value?

[ChrisNZ · Accepted solution]

Like this?

proc sql;
select HAVE.ID
     , HAVE.KEY
     , ifn(key.KEY is null and YN=9,0,have.YN) as YN
from HAVE
left join KEY 
on have.KEY=key.KEY;
quit;

ID	KEY	YN
1	20010305	0
1	20010408	9
1	20010510	0
1	20010511	0
2	20030309	9
2	20030408	0
2	20030607	0
2	20030715	0
3	20040913	9
3	20041011	0
3	20050205	0
3	20060305	0