Help using Base SAS procedures

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

Reply
Contributor
Posts: 65

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

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.

Super User
Posts: 17,796

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

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

 

Super User
Posts: 17,796

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

Trusted Advisor
Posts: 1,204

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

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;

Ask a Question
Discussion stats
  • 3 replies
  • 255 views
  • 0 likes
  • 3 in conversation