removing bad values

[Elliott]

Hi, I am creating a file and have found that one of my fields has a lot of garbage in it and I need to exclude those records.

it is an account_no field and I am trying to exclude values that have 1 zero or multiple zeros or multiple 9's.  I have tried using

proc sql;

create table x as 

select * from y where account_no not like '0000000000%' or account_no not like '9999999999%';

quit;

but that is not doing anything.

I have a data step that I am using a delete

If account_no in (' ','0','*') then delete;

that works for those values but not for the one's that have the multiple zeros or 9's.

How can I do this?

Thanks,

Elliott

[Astounding]

At a minimum, you should start with your SQL code, but replace OR with AND.

Any possible value is not equal to "A" or not equal to "B".

[ballardw]

You should more clearly define exactly what you mean by "multiple 0's" or 'multiple 9's"

I can really see these as being valid if fake account numbers:

123045600

123945699

both have multiple 0 or 9. Would those be excluded?

Or do you mean ALL 0 or ALL 9? Or something else?

[Elliott]

multiple zero's  = 000000000000000 or 0000 or 00000000000000000000000

multiple 9's = 99999999999999# or 99999999 or 99999999999999999

[Patrick]

@Elliott

Providing sample data via a SAS data step helps all of us to avoid missunderstandings.

If below uses representative sample data then the code should do the job for you.

data have;
  infile datalines truncover;
  input account_no $40.;
  datalines;
123045600
123945699
000000000000000
0000
00000000000000000000000
99999999999999 
99999999 
99999999999999999
0999090
;
run;

proc sql; 
  select * 
  from have 
  where not missing(compress(account_no,'9')) and not missing(compress(account_no,'0')) 
  ;
quit;

[Elliott]

this cares for everything except for the values that have 99999999999999#, what do I do about that type of data.

Thanks,

[Patrick]

@Elliott

---

@Elliott wrote:
this cares for everything except for the values that have 99999999999999#, what do I do about that type of data.

Thanks,

---

What can you think of?

[Ksharp]

Post some data and output would be better to explain your question.

data have;
  infile datalines truncover;
  input account_no $40.;
  datalines;
123045600
123945699
000000000000000
0000
00000000000000000000000
99999999999999 
99999999 
99999999999999999
0999090
;
run;
data want;
 set have;
 if prxmatch('/^(0+|9{2,})/',account_no) then delete;
 run;