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.
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)
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)
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!!!
I guess you could have replaced
min(NEGHISDAYS) <= 105
by
min(NEGHISDAYS) between 1 and 105
That worked! I'm still relativley new to SAS, so using "between # and #" was out of my radar.
Thanks again for all the help 😄
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.
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.