topic Remove numeric values from a number of fields in SAS Enterprise Guide

Remove numeric values from a number of fields

Haydn — Thu, 30 Jul 2015 03:51:47 GMT

Hi,

I have the below to remove numeric values from the fields called 'Outcome_Notes' and 'Notes'. The reason being, these are free text fields and even though staff are instructed not to input reference numbers or phone numbers in these fields, they do. I have 48 fields of free text.

Outcome_Notes=compress(Outcome_Notes,'(0,1,2,3,4,5,6,7,8,9) ');

Notes=compress(Notes,'(0,1,2,3,4,5,6,7,8,9) ');

Is there a way of removing numeric values from numerous fields without repeating the above 48 times?

Kind Regards

Haydn

Re: Remove numeric values from a number of fields

Steelers_In_DC — Thu, 30 Jul 2015 12:12:05 GMT

It seems like you have the solution to me so I might be missing the question. You can use the 'd' modifier to remove all digits from any string. Run this and let me know if this helps:

data have;

infile cards dsd;

format outcome_notes notes $50.;

input outcome_notes notes;

cards;

123456987987631asdfwerewer,3asd1af6er6da1f

adsfadfgsgdrf3216587987adsfaesr,ad3s21fa6we4

asdfaer53467asdf354aew6r8,adsfaser32164648

aerraqewf1165a98s7er,32165a4serasdfas

asdfawe6r54251,asdrfawe321a65sd46d321

awerwedf1216476,adsfae32146546

awered32165849684165tgre,adsf321654er

;

run;

/*I prefer to use a new variable to maintain the original but you don't have to*/

data want;

set have;

/*new_ will maintain the original column*/

new_outcome_notes = compress(outcome_notes,1,'d');

new_notes = compress(notes,1,'d');

/*using the same name will overwrite the original with the desired*/

outcome_notes = compress(outcome_notes,1,'d');

notes = compress(notes,1,'d');

run;

Re: Remove numeric values from a number of fields

TomKari — Thu, 30 Jul 2015 12:59:25 GMT

Might you be better off to change digits to something like the '#' symbol, to retain the context of the comment?

Tom

Re: Remove numeric values from a number of fields

RW9 — Thu, 30 Jul 2015 13:12:35 GMT

To do multiple columns the same way, the quickest way is to array them:

data want;

set have;

array to_do{3} column1 anothercolumn somethingelse;

do i=1 to 3;

to_do{i}=compress(to_do,'()','d');

end;

run;

Re: Remove numeric values from a number of fields

Haydn — Thu, 30 Jul 2015 23:59:08 GMT

Thanks RW9,

i thought I'd test out your code on the two fields mentioned in my original post.

I tried using your code as follows:

array to_do{2} Outcome_Notes Notes;

do i=1 to 2;

to_do{i}=compress(todo,'()','d');

end;

and also

array to_do{2} Outcome_Notes Notes;

do i=1 to 2;

to_do{i}=compress(todo,'(0,1,2,3,4,5,6,7,8,9)','d');

end;

Both the above deleted everything out of the fields of Outcome_Notes and Notes.

Can you please point out where I'm going wrong?

Re: Remove numeric values from a number of fields

Haydn — Fri, 31 Jul 2015 00:00:16 GMT

Hi Tom,

ideally it would be better to replace the numerical characters with #

Re: Remove numeric values from a number of fields

Haydn — Fri, 31 Jul 2015 04:47:11 GMT

Hi guys,

Thanks for your help, I've manage to make it work for me.

array RemoveNumbers{2} Outcome_Notes Notes;

do i=1 to 2;

RemoveNumbers{i}=compress(RemoveNumbers{i},(),'d');

end;

Kind Regards

Haydn

Re: Remove numeric values from a number of fields

RW9 — Fri, 31 Jul 2015 08:36:16 GMT

Yes, there was a typo in my program, updated below with replace numbers with #:

data have;

column1="sdlalajs123sdsd"; anothercolumn="1234fgg334"; somethingelse="1234";

run;

data want (drop=i j);

set have;

array to_do{3} column1 anothercolumn somethingelse;

do i=1 to 3;

do j=1 to length(to_do{i});

if char(to_do{i},j) in ('1','2','3','4','5','6','7','8','9','0') then substr(to_do{i},j,1)="#";

end;

run;

You could also use perl regular expressions to do that I would imagine, but don't have time to look it up.

Re: Remove numeric values from a number of fields

Haydn — Mon, 03 Aug 2015 01:16:22 GMT

Thanks RW9,

Your code worked like a charm. Thanks to you and others who have responded.

Cheers

Haydn