Select observations based on condition

Reply
Occasional Contributor
Posts: 5

Select observations based on condition

Hello everyone,

I am having an issue selecting at subset of observations. I want to select based on the condition that each participant for each event, the subset have 3 or more values (>= .02) of a particular variable (VAR).

For example,

PID     Event     VAR

1923     1          0

1923     1          .02

1923     1          .03

1923     1          .04

1923     1          .002

1923     1          0

2003     1          0

2003     1          .02

2003     1          .06

2003     1          .07

2003     1          .003

2003     1          0


So in this example, both participants would be selected because they have 3 or more observations in VAR that are >= .02. Please note that each participant has multiple events so I need to check each event for each participant.


Any direction would be greatly appreciated!

Thank you!

Super User
Super User
Posts: 7,401

Re: Select observations based on condition

Hi,

Something along these lines:

data have;
  attrib PID Event VAR format=best.;
  input pid event var;
datalines;
1923     1          0

1923     1          .02

1923     1          .03

1923     1          .04

1923     1          .002

1923     1          0
;
run;


proc sql;
  create table WANT as
  select  *
  from  (select PID,EVENT,VAR,case when VAR > 0.02 then "Y" else "N" end as FLAG from HAVE)
  group by PID,EVENT
  having  COUNT(FLAG)>1;
quit;

Trusted Advisor
Posts: 1,204

Re: Select observations based on condition

data have;
input PID     Event     VAR;
datalines;
1923     1          0
1923     1         .02
1923     1         .03
1923     1         .04
1923     1         .002
1923     1          0
2003     1          0
2003     1         .02
2003     1         .06
2003     1         .07
2003     1         .003
2003     1          0
;

proc sql;
select * from have
group by pid,event
having sum(var>=0.02)>=3;
quit;

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