topic how to remove error records in SAS Programming

how to remove error records

deleted_user — Sat, 11 Dec 2010 18:34:22 GMT

Hi,

I have quite a big table with lots of fields. Wanted to filter the records looking for a error condition.

Condition is there are member id's and ssn, one member can have only one ssn and the reverse is ony one ssn must be present for one member.

But the table I have contains a same SSN for multiple member id's and multiple member ssn for same same member id's.

There many records for same member and couple of records are like this.

I dont get to know how to filter these members.

Would appreciate you guys give a solution for this.

Thanks,

Re: how to remove error records

sbb — Sat, 11 Dec 2010 19:17:44 GMT

Suggest sorting your file and then using a DATA step, investigate using BY GROUP PROCESSING (see Google search below - you will be using FIRST. and/or LAST. in your code) to detect and DELETE (or OUTPUT to another separate SAS file) your "exception condition" observations. Or you may be able to use SAS PROC SORT with NODUPKEY to remove duplicates and/or again with PROC SORT use the DUPOUT= parameter to save off your duplicates (based on the BY statement variable list).

First off, I would recommend you address each of the statement requirements individually as a task and then work to integrate/combine the tasks so that your final output file(s) address the data manipulation objective.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

data step by group processing site:sas.com Message was edited by: sbb

Re: how to remove error records

SPR — Mon, 13 Dec 2010 15:45:26 GMT

Hello Activa,

This is a possible solution:
[pre]
data i;
id=1; ssn=100; output;
id=1; ssn= 90; output;
id=1; ssn= 80; output;
id=2; ssn=200; output;
id=3; ssn=200; output;
id=4; ssn=400; output;
run;
proc sort data=i out=s;
by ID;
run;
data BadID;
set s;
if NOT (First.ID=1 and Last.ID=1);
by ID;
run;
proc sort data=i out=s;
by SSN;
run;
data BadSSN;
set s;
if NOT (First.SSN=1 and Last.SSN=1);
by SSN;
run;
[/pre]
Sincerely,
SPR