DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

Hello,

 

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

IDVar1
A1
B2
C3
D4
E5

 

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:

IDVar1
A1
B2
C3
D.
E.

 

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


Accepted Solutions
Solution
‎04-16-2018 03:58 PM
Super User
Posts: 2,061

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

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;

View solution in original post


All Replies
Super User
Posts: 24,018

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

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.

 

 

Solution
‎04-16-2018 03:58 PM
Super User
Posts: 2,061

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

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;
Occasional Contributor
Posts: 10

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

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?

Super User
Posts: 2,061

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

[ Edited ]

in that case, take the refvar as var Smiley Happy

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 

Occasional Contributor
Posts: 10

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

[ Edited ]
Posted in reply to novinosrin

Works as well. Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 147 views
  • 0 likes
  • 3 in conversation