BookmarkSubscribeRSS Feed
aishajennifer1
Calcite | Level 5

I have the following dataset

 

subjectflagbmi
1123
1123
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:

subjectflagbmi
1123
1123
1 23

 

I tried proc sql;

create table want as

select *

from have

having = null;quit;

 

Really stuck.

7 REPLIES 7
Patrick
Opal | Level 21

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
Lapis Lazuli | Level 10

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;
-unison
KachiM
Rhodochrosite | Level 12

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
Super User
create table want as
select *
from have
where subject in (select subject from have where flag=1)
;
quit;
ed_sas_member
Meteorite | Level 14

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
Diamond | Level 26
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;
--
Paige Miller
Reeza
Super User

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. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 856 views
  • 4 likes
  • 8 in conversation