BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TXSASneophyte
Obsidian | Level 7

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. 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
PGStats
Opal | Level 21

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
TXSASneophyte
Obsidian | Level 7

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
Opal | Level 21

I guess you could have replaced 

 

min(NEGHISDAYS) <= 105

by

 

min(NEGHISDAYS) between 1 and 105

 

PG
TXSASneophyte
Obsidian | Level 7

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

 

Thanks again for all the help 😄

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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