SQL Filter Rows for Duplicate ID Based on Condition Different Column

Question

I want to return all rows for repeated ID where a "value" is greater than 30

I have this table

I want this table

This is my query, but it is not working. Any thoughts?

create table test as 
select *
from A
where ID in (select ID from A where value >= 30);
quit;```

Answer 1

Try this

data have;
input ID value;
datalines;
1 1  
1 2  
1 3  
1 4  
1 31 
2 1  
2 2  
2 3  
2 4  
2 5  
;

proc sql;
   create table want as
   select *
   from have
   group by ID
   having sum(value > 30) > 0
   ;
quit;