topic Re: Put the dummy variable equal to one if the value is equal to a variable in another dataset in SAS Procedures

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

m1986MM — Fri, 11 Sep 2015 19:47:03 GMT

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 — Fri, 11 Sep 2015 20:12:02 GMT

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 — Fri, 11 Sep 2015 20:12:55 GMT

stat_sas — Fri, 11 Sep 2015 20:15:04 GMT

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;