topic Re: extract certain ids in SAS Programming

extract certain ids

aishajennifer1 — Fri, 27 Dec 2019 01:22:43 GMT

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.

Re: extract certain ids

Patrick — Fri, 27 Dec 2019 02:10:04 GMT

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;

Re: extract certain ids

unison — Fri, 27 Dec 2019 02:49:40 GMT

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;

Re: extract certain ids

KachiM — Fri, 27 Dec 2019 04:53:54 GMT

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;

Re: extract certain ids

Ksharp — Fri, 27 Dec 2019 11:48:24 GMT

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

Re: extract certain ids

ed_sas_member — Fri, 27 Dec 2019 12:15:57 GMT

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;

Re: extract certain ids

PaigeMiller — Fri, 27 Dec 2019 12:26:11 GMT

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;

Re: extract certain ids

Reeza — Fri, 27 Dec 2019 15:55:25 GMT

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.