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
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".
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?
multiple zero's = 000000000000000 or 0000 or 00000000000000000000000
multiple 9's = 99999999999999# or 99999999 or 99999999999999999
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.