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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

righcoastmike
Quartz | Level 8

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.

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
righcoastmike
Quartz | Level 8
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.
Reeza
Super User

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

 

 



 

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

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
  • 6 replies
  • 471 views
  • 0 likes
  • 4 in conversation