BookmarkSubscribeRSS Feed
Sathish_jammy
Lapis Lazuli | Level 10

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!

2 REPLIES 2
andreas_lds
Jade | Level 19

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;

 

 

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 386 views
  • 0 likes
  • 3 in conversation