topic Create dummy variable for group in SAS Procedures

Create dummy variable for group

sasvader — Fri, 28 Aug 2015 07:18:33 GMT

Hi,

I am trying to create the dummy variable Want which is = 1 if there is any observation that has a value in Have.

E.g. Want = 1 for all observations of A1 because there is at least one observation with a value in Have. Want = 0 for B1.

Is there a way to code this? Thanks for your help!

Sample data:

ID	Have	Want
A1	.	1
A1	.	1
A1	100	1
A1	.	1
A1	.	1
A1	.	1
A1	.	1
A1	.	1
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0
B1	.	0

Regards,

Daniel

Re: Create dummy variable for group

FatCaptain — Tue, 08 Sep 2015 15:20:47 GMT

data have ;
id = 'A1' ; have =   . ; output ;
id = 'A1' ; have = 100 ; output ;
id = 'A1' ; have =   . ; output ;
id = 'B1' ; have =   . ; output ;
id = 'B1' ; have =   . ; output ;
id = 'B1' ; have =   . ; output ;
run ;

proc sql ;
create table want as
select have.*,
case when id in
   (select distinct id
   from   have
   where have is not missing) then 1 else 0 end as want
from have ;
quit ;

Re: Create dummy variable for group

MikeZdeb — Tue, 08 Sep 2015 16:03:29 GMT

Hi, here's a data step solution ...

data want;
want = 0;
do until (last.id);
set have (in=one) have;
by id;
if one and have then want = 1;
if ^one then output;
end;
run;

It's based on Howard Schreier's idea of a "self-interleave" ...

Interleaving a Dataset with Itself: How and Why

http://www.lexjansen.com/nesug/nesug03/cc/cc002.pdf