topic Re: Remove the 2nd special character in a cell in SAS Programming

Remove the 2nd special character in a cell

Sathish_jammy — Tue, 04 May 2021 10:18:43 GMT

Dear Experts,

I have a dataset with a column of year in values. Most of the values are incorrect while enter the data.

Here I attach the sample dataset along with expected output.

Have	Want
6.9.	6.9
7.8.	7.8
6.2.	6.2
8.9.2	8.92
4.5.2	4.52
6.5.7	6.57

Kindly suggest a code to get the given output.

Thanks in advance!

Re: Remove the 2nd special character in a cell

andreas_lds — Tue, 04 May 2021 10:52:07 GMT

The function "notdigit": Searches a character string for any character that is not a digit, and returns the first position at which that character is found. The function has a second parameter allowing to select from which position search is started. So you could loop until notdigit returns 0, using cats and substr to remove the unwanted content. Some easier solution are possible, but i don't know enough about the data you have: the second step assumes, that the special char to keep is always the dot and that ^ is never part of the string.

data want_a;
   set have;
   
   want = have;
   s = notdigit(want);
   put s=;
   
   do while (s > 0);
      s = notdigit(trim(want), s+1);
     
      if s > 0 then do;
         want = cats(substr(want, 1, s-1), substr(want, s+1));
      end;
   end;
run;


data want_b;
   set have;
   
   want = have;
   substr(want, notdigit(want), 1) = '^';
   want = compress(want, '^', 'kd');
   want = translate(want, '.', '^');
run;

Re: Remove the 2nd special character in a cell

Ksharp — Tue, 04 May 2021 11:03:35 GMT

data have;
input Have $;
cards;
6.9.	6.9
7.8.	7.8
6.2.	6.2
8.9.2	8.92
4.5.2	4.52
6.5.7
;
data want;
 set have;
 call scan(have,-1,p,l,'.');
 want=cats(substr(have,1,l-1),substrn(have,p));
 drop p l;
run;