extract certain ids

[aishajennifer1]

I have the following dataset

subject	flag	bmi
1	1	23
1	1	23
1		23
2		25
2		25
2		25

What I want to do: If the patient has any instance of flag = 1, I would like to pull all the data records into a diff dataset. Want:

subject	flag	bmi
1	1	23
1	1	23
1		23

I tried proc sql;

create table want as

select *

from have

having = null;quit;

Really stuck.

[Patrick]

Below two options how to get what you want.

Option 1 will change the sort order of your source data, option 2 won't.

data have;
  infile datalines truncover dsd;
  input subject flag bmi;
  datalines;
1,1,23
1,1,23
1,,23
2,,25
2,,25
2,,25
;

/* option 1 */
title 'option 1';
proc sql;
/*  create table want as*/
    select * 
    from have 
    group by subject
    having max(flag)=1
    ;
quit;
title;

/* option 2 */
title 'option 2';
data want;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'have(where=(flag=1))');
      h1.defineKey('subject');
      h1.defineDone();
    end;
  set have;
  if h1.check()=0 then output;
run;
proc print;
run;
title;

[unison]

Another option. (thank you @Patrick for the data step)

data have;
  infile datalines truncover dsd;
  input subject flag bmi;
  datalines;
1,1,23
1,1,23
1,,23
2,,25
2,,25
2,,25
;

proc sort data=have(where=(flag=1)) nodupkey out=dedup(drop=bmi);
by subject;
run;

data want;
merge have(in=a) dedup(in=b);
by subject;
if a and b;
run;

[KachiM]

Hi @aishajennifer1 ,

Yet another option.

Use double DoW. It requires presorting by Subject.

proc sort data = have;
by subject;
run;

data want;
   do until(last.subject);
      set have;
      by subject;
      if flag = 1 then hold = 1;
   end;
   do until(last.subject);
      set have;
      by subject;
      if hold = 1 then output;
   end;   
drop hold; 
run;

[Ksharp]

create table want as
select *
from have
where subject in (select subject from have where flag=1)
;
quit;

[ed_sas_member]

Hi @aishajennifer1 

You can try this:

proc sql;
	create table want as
	select *
	from have
	where subject in (select distinct(subject) from have where flag = 1);
quit;

[PaigeMiller]

proc summary data=have nway;
     class id;
     var flag;
     output out=maxflag max=maxflag;
run;
data want;
     merge have maxflag;
     by id;
     if maxflag=1;
run;

[Reeza]

1. Get a list of ID's that have a 1 indicator

2. Merge or filter your second data set by the list in the first. 

For a beginner the best solution IMO would be @ed_sas_member solution.