topic Removing a large number of observations based on a series of patient ID's in SAS Programming

Removing a large number of observations based on a series of patient ID's

righcoastmike — Mon, 17 Jul 2017 19:25:28 GMT

Hello Everyone,

I have a large list of patient records (over 100k). After some initial analysis I have come up with a list of 1720 patient ID's that need to be removed from the list. Any suggestions as to how to go about this without writing the following 1720 times?

if patient_id = (patient_id) then DELETE;

Any thoughts would be much appreciated.

Thanks so much

Re: Removing a large number of observations based on a series of patient ID's

art297 — Mon, 17 Jul 2017 19:34:15 GMT

Merge the two files, but only keep those IDs which aren't in both files.

Art, CEO, AnalystFinder.com

Re: Removing a large number of observations based on a series of patient ID's

PaigeMiller — Mon, 17 Jul 2017 20:03:24 GMT

Art has the best idea, if the patient IDs to be removed are somehow indicated in a SAS dataset. But you didn't say that the information was in the dataset, you said you have a list ... somehow you have to either type the patient IDs as follows:

if patient_id in (2300091,2300392,2407702, ...) then delete; 
/* and don't make any typographical errors */

Or otherwise find a way to indicate these 1720 patient IDs

Re: Removing a large number of observations based on a series of patient ID's

Reeza — Mon, 17 Jul 2017 20:03:06 GMT

Use SQL NOT IN

Assuming your full dataset is A and your partial dataset is B,

proc sql;
create table want as
select *
from table a
where id not in (select id from b);
quit;

@righcoastmike wrote:

Hello Everyone,

I have a large list of patient records (over 100k). After some initial analysis I have come up with a list of 1720 patient ID's that need to be removed from the list. Any suggestions as to how to go about this without writing the following 1720 times?

if patient_id = (patient_id) then DELETE;

Any thoughts would be much appreciated.

Thanks so much

Re: Removing a large number of observations based on a series of patient ID's

righcoastmike — Tue, 18 Jul 2017 01:58:21 GMT

This worked like a charm.

Thanks so much!

For anyone looking to use this solution, you can find instructions on how to merge tables while only keeping the records that don't match here:

http://support.sas.com/kb/40/414.html

Thanks again.

Rightcoast.

Re: Removing a large number of observations based on a series of patient ID's

righcoastmike — Tue, 18 Jul 2017 01:59:37 GMT

Luckily I had the patient ID's in both tables. I'll be more specific next time, but thanks so much. I'm sure this will come in handy down the line.

Re: Removing a large number of observations based on a series of patient ID's

righcoastmike — Tue, 18 Jul 2017 02:00:44 GMT

Thankfully I didn't need to go full Proc SQL on this one. Still though, really helpful for future challenges. Thanks so much!