DATA Step, Macro, Functions and more

removing bad values

Reply
Frequent Contributor
Posts: 91

removing bad values

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

Super User
Posts: 6,541

Re: removing bad values

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".

Super User
Posts: 13,046

Re: removing bad values

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?

Frequent Contributor
Posts: 91

Re: removing bad values

multiple zero's  = 000000000000000 or 0000 or 00000000000000000000000

 

multiple 9's = 99999999999999# or 99999999 or 99999999999999999

Respected Advisor
Posts: 4,547

Re: removing bad values

@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;
Frequent Contributor
Posts: 91

Re: removing bad values

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

Thanks,
Respected Advisor
Posts: 4,547

Re: removing bad values

@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?

Super User
Posts: 10,615

Re: removing bad values

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;


Ask a Question
Discussion stats
  • 7 replies
  • 219 views
  • 0 likes
  • 5 in conversation