DATA Step, Macro, Functions and more

Unique values across variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Unique values across variables

[ Edited ]

Hi,

 

i have two variables in a data set and i want to see if the first variable has values which also exists in the other variable. For example:

 

Varone     Vartwo           Flag

 

 1               4                  0

2                7                   0

7                5                   1

4                8                   1

 

 

So, i want to check every single one of the values in Varone; if the value in Varone exists anywhere in variable Vartwo, then i want a flag value that says 1 and zero otherwise.

 

thanks


Accepted Solutions
Solution
‎04-27-2017 05:55 PM
Respected Advisor
Posts: 4,662

Re: Unique values across variables

 

proc sql;
create table want as
select 
	*,
	varone in (select vartwo from have) as flag
from have;
quit;
PG

View solution in original post


All Replies
Frequent Contributor
Posts: 93

Re: Unique values across variables

One method (there are many):

 

proc sql;
create table combvars as
select have.var1, have2.var2 
from have, have as have2
where have.var1 = have2.var2;
quit;
Solution
‎04-27-2017 05:55 PM
Respected Advisor
Posts: 4,662

Re: Unique values across variables

 

proc sql;
create table want as
select 
	*,
	varone in (select vartwo from have) as flag
from have;
quit;
PG
PROC Star
Posts: 264

Re: Unique values across variables

this should work

 

data abc;

input varone vartwo;

datalines;

1 4

2 7

7 5

4 8

;

run;

proc sql;

select varone, vartwo, case when varone=var1 then 1 else 0 end as flag

from

(select varone, vartwo from abc)a

full join

(select varone as var1

from abc

where varone in (select vartwo from abc))x

on a.varone =x.var1;

Occasional Contributor
Posts: 10

Re: Unique values across variables

Thank you all for your replies, i really appreciate it. It worked

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 133 views
  • 4 likes
  • 4 in conversation