how to clean up variable accordinly

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

how to clean up variable accordinly

I have incoming data which contains a company id in the form of: 1234567-1-123 (example). This needs to be turned into such 1234567-1. BUT the same variable also sometimes holds a social security code in the form of 123456-123A (example, the last one is a letter) and this needs to be kept as is.



Accepted Solutions
Solution
‎09-10-2014 09:18 AM
PROC Star
Posts: 7,468

Re: how to clean up variable accordinly

data have;

  informat company $20.;

  input company;

  cards;

1234567-1-123

123456-123A

;

data want;

  set have;

  if count(company,'-') gt 1 then

   company=substr(company,1,find(company,'-',-25)-1);

run;

View solution in original post


All Replies
Regular Contributor
Posts: 233

Re: how to clean up variable accordinly

data have;
input id $15.;
cards;
1234567-1-123
123456-123Aa 
;
run;

data want;
set have;
if index(id,'A') gt 0 then id1=id;
else id1=substr(id,1,9);
run;

Capture.JPG

Solution
‎09-10-2014 09:18 AM
PROC Star
Posts: 7,468

Re: how to clean up variable accordinly

data have;

  informat company $20.;

  input company;

  cards;

1234567-1-123

123456-123A

;

data want;

  set have;

  if count(company,'-') gt 1 then

   company=substr(company,1,find(company,'-',-25)-1);

run;

Super Contributor
Posts: 644

Re: how to clean up variable accordinly

A more robust approach would be to use the compress function to detect an alpha character.

     if missing(compress(company, ,'KA')) then ...

The compress throws away, in this case, all non alpha characters - if you are left with a non missing result you need to preserve the original value of company

Richard

Super User
Posts: 10,020

Re: how to clean up variable accordinly

If there are some data like :   1234567-1-123-2  ?

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 290 views
  • 0 likes
  • 5 in conversation