topic Re: Convert a variable's values to missing using a reference table in SAS Programming

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

torvyle — Mon, 16 Apr 2018 16:37:12 GMT

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

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.

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

Reeza — Mon, 16 Apr 2018 16:44:54 GMT

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.

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

novinosrin — Mon, 16 Apr 2018 16:45:21 GMT

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;

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

torvyle — Mon, 16 Apr 2018 20:03:17 GMT

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?

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

novinosrin — Mon, 16 Apr 2018 20:17:24 GMT

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

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

torvyle — Mon, 16 Apr 2018 20:52:13 GMT

Works as well. Thank you!