Convert a variable's values to missing using a reference table

[torvyle]

Hello,

Say that I have dataset1 with ID and variable1, like this:

ID	Var1
A	1
B	2
C	3
D	4
E	5

I also have dataset2, which contains a reference variable, like this:

RefVar

1

2

3

How do I modify dataset1 such that any Var1's values not found in dataset2's Refvar will be converted to missing? The resulting dataset1 should look like this:

ID	Var1
A	1
B	2
C	3
D	.
E	.

The actual dataset has a lot of values for "Var1" and "RefVar" so a PROC SQL solution would be ideal, I believe.

[Reeza]

I would use dataset2 to create a format and then apply the format as desired. 

You can use the hlo='O' to create the other record to assign it to missing if desired.

[novinosrin · Accepted solution]

data one;
input ID $	Var1;
datalines;
A	1
B	2
C	3
D	4
E	5
;

data ref;
input RefVar;
datalines;
1
2
3
;

proc sql;
create table want as
select id ,var1+refvar-refvar as var1
from one left join ref on one.var1=ref.refvar
order by id,var1;
quit;

[torvyle]

Thank you very much.

If Var1 and Refvar variables happen to be string would creating a format using the reference dataset, like what Reeza said, be the only option or is there a PROC SQL code for that? Would I have to create a new thread for this?

[novinosrin]

in that case, take the refvar as var 🙂

data one;
input ID $	Var1 $;
datalines;
A	a
B	b
C	c
D	d
E	e
;

data ref;
input RefVar $;
datalines;
a
b
c
;


proc sql;
create table want as
select id ,refvar as var1
from one left join ref on one.var1=ref.refvar
order by id,2;
quit;

This works for both string and numerics as you can tell from the code by replacing the var1 with ref from table2 

[torvyle]

Works as well. Thank you!