Removing/Deleting Observations

[TXSASneophyte]

Hi Everyone,

I was wondering if someone could help me remove/delete some observations on my dataset. 

In my dataset, members can have multiple observations, and the number of observations vary.  I have this variable called "NEGHISDAYS", and it ranges between 1 to 365. I want to delete the member and all their observations from my dataset if any of their observations has a "NEGHISDAYS" between 1 & 105. The code I have right now only deletes the specific observations that falls between 1 & 105 but the other observations remain, but that is not what I want. I want the member and all their records removed. 

Here's an example:

ID     NEGHISDAYS

1          .

1          25

1          107

1          .

The example has one member with four observations; one observation (observation 2) falls between 1&105. My current code removes observations 2 but keeps the other observations. However, since observation 2 falls between 1&105, I want the member and all their records (observations 1 -4) removed.  

I am using SAS 9.2. 

[PGStats · Accepted solution]

Use this query:

proc sql;

create table toBeDeleted as
select ID 
from myData
group by ID
having min(NEGHISDAYS) <= 105;
 
delete from myData
where ID in (select ID from toBeDeleted);

drop table toBeDeleted;
quit;

(untested)

[TXSASneophyte]

Your code worked! There was a minor kink at first because there are missing values, which your code deleted even though I wanted to keep them. I didn't know how to tweak the code you provided to keep the missing values if they did not violate my criteria of falling between 1 & 105. However, I did have another variable without any missing values that I used with your code and it worked fantastically well!!!

Thanks for all the help!!! 

[PGStats]

I guess you could have replaced 

min(NEGHISDAYS) <= 105

by

min(NEGHISDAYS) between 1 and 105

[TXSASneophyte]

That worked! I'm still relativley new to SAS, so using "between # and #" was out of my radar.

Thanks again for all the help 😄