Put the dummy variable equal to one if the value is equal to a variable in another dataset

[m1986MM]

Hi everyone, 

Because I could not find my question amng the latest ones, so I send it again. If you find it somewhere else as well, I apologize for that.

I use the following code to create a subset when the value of a variable is equal to a variable in another dataset.

proc sql;

create table want as select *

from have1 

where id in(select id from have2); quit;

However, I don't want to create a subset, but I want to say id_dummy=1 if id is in have2.

I appreciate any suggestion.

[Reeza]

If you don't have duplicates you can use a merge and check for matches.

If you have duplicates you can remove them and then use a merge and check for matches.

You could use an inline query with a CASE statement

CASE WHEN (SELECT A.NAME FROM HAVE AS _X WHERE A.NAME=_X.NAME) IS NOT NULL THEN 1
ELSE 0

[Reeza]

You're previous question FYI https://communities.sas.com/t5/SAS-Procedures/where-statement-when-a-variable-equals-another-variabl...

[stat_sas]

Try something like this:

data a;
input id;
datalines;
1
2
3
4
5
;

data b;
input id;
datalines;
2
3
;

proc sql;
select aa.*,case when aa.id=bb.id then 1 else 0 end as id_dummy from a aa left join b bb
on aa.id=bb.id;
quit;