BookmarkSubscribeRSS Feed
Elliott
Obsidian | Level 7

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

7 REPLIES 7
Astounding
PROC Star

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
Super User

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
Obsidian | Level 7

multiple zero's  = 000000000000000 or 0000 or 00000000000000000000000

 

multiple 9's = 99999999999999# or 99999999 or 99999999999999999

Patrick
Opal | Level 21

@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
Obsidian | Level 7
this cares for everything except for the values that have 99999999999999#, what do I do about that type of data.

Thanks,
Patrick
Opal | Level 21

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


hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2283 views
  • 0 likes
  • 5 in conversation