topic Re: Removing/Deleting Observations in SAS Programming

Removing/Deleting Observations

TXSASneophyte — Thu, 23 Jun 2016 17:09:06 GMT

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.

Re: Removing/Deleting Observations

PGStats — Thu, 23 Jun 2016 17:48:02 GMT

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)

Re: Removing/Deleting Observations

TXSASneophyte — Thu, 23 Jun 2016 18:17:42 GMT

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!!!

Re: Removing/Deleting Observations

PGStats — Thu, 23 Jun 2016 18:24:47 GMT

I guess you could have replaced

min(NEGHISDAYS) <= 105

min(NEGHISDAYS) between 1 and 105

Re: Removing/Deleting Observations

TXSASneophyte — Thu, 23 Jun 2016 19:41:49 GMT

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

Thanks again for all the help 😄