Desktop productivity for business analysts and programmers

Remove numeric values from a number of fields

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Remove numeric values from a number of fields


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


Accepted Solutions
Solution
‎07-31-2015 12:47 AM
Contributor
Posts: 37

Re: Remove numeric values from a number of fields

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

View solution in original post


All Replies
Valued Guide
Posts: 854

Re: Remove numeric values from a number of fields

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;

Trusted Advisor
Posts: 1,056

Re: Remove numeric values from a number of fields

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

Tom

Contributor
Posts: 37

Re: Remove numeric values from a number of fields

Hi Tom,

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

Solution
‎07-31-2015 12:47 AM
Contributor
Posts: 37

Re: Remove numeric values from a number of fields

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,207

Re: Remove numeric values from a number of fields

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;

Contributor
Posts: 37

Re: Remove numeric values from a number of fields

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?

Esteemed Advisor
Esteemed Advisor
Posts: 7,207

Re: Remove numeric values from a number of fields

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;

     end;

run;

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

Contributor
Posts: 37

Re: Remove numeric values from a number of fields

Thanks RW9,

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

Cheers

Haydn

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 672 views
  • 10 likes
  • 4 in conversation