DATA Step, Macro, Functions and more

Removing/Deleting Observations

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Removing/Deleting Observations

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. 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎06-23-2016 02:13 PM
Respected Advisor
Posts: 4,662

Re: Removing/Deleting Observations

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)

PG

View solution in original post


All Replies
Solution
‎06-23-2016 02:13 PM
Respected Advisor
Posts: 4,662

Re: Removing/Deleting Observations

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)

PG
Contributor
Posts: 45

Re: Removing/Deleting Observations

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

Respected Advisor
Posts: 4,662

Re: Removing/Deleting Observations

I guess you could have replaced 

 

min(NEGHISDAYS) <= 105

by

 

min(NEGHISDAYS) between 1 and 105

 

PG
Contributor
Posts: 45

Re: Removing/Deleting Observations

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

 

Thanks again for all the help Smiley Very Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 269 views
  • 0 likes
  • 2 in conversation