topic Re: removing bad values in SAS Programming

removing bad values

Elliott — Fri, 15 Sep 2017 20:40:30 GMT

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

Re: removing bad values

Astounding — Fri, 15 Sep 2017 20:55:32 GMT

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

Re: removing bad values

ballardw — Fri, 15 Sep 2017 20:58:00 GMT

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?

Re: removing bad values

Elliott — Fri, 15 Sep 2017 23:27:53 GMT

multiple zero's = 000000000000000 or 0000 or 00000000000000000000000

multiple 9's = 99999999999999# or 99999999 or 99999999999999999

Re: removing bad values

Patrick — Sat, 16 Sep 2017 00:42:52 GMT

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

Re: removing bad values

Elliott — Sat, 16 Sep 2017 11:56:59 GMT

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

Thanks,

Re: removing bad values

Patrick — Sat, 16 Sep 2017 21:00:03 GMT

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

Re: removing bad values

Ksharp — Sun, 17 Sep 2017 13:13:00 GMT

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;