BookmarkSubscribeRSS Feed
avbraga
Calcite | Level 5

Hello all,

I have a piece of code where it looks at an ID variable that should only have numbers in it. But I created some test data where the IDs are messed up and have some letters in it.

The goal is to transform the letters (a, b, c...) into zeros (0). The code does the job well if and only if there is only the same type of character in the string. Example:

551215A becomes 5512150

57A1215 becomes 5701215

55Cc13c becomes 5500130.

But IDs like:

55Aab5A became 55AA05A (Only the B was transformed to zero - I would like it to be 5500050)

abCDE5g became ABCDE50 (only the G was transformed to zero - I would like it to be 0000050).

That is, only the last letter the do loop sees in the observation gets transformed to zero.

Bellow follows the code:

/*///////////////////////////////////////////////////////////*/

DATA test_id;

INPUT claimid $;

DATALINES;

551215A

55Cc13c

541215B

abCDE5g

551B159

6512157

57A1215

55Aab5A

5682154

581915c

5519155

;

run;

DATA test_id2;

  set test_id (rename = (claimid = orig_claimid));

claim_num = orig_claimid + 0;

claimid = upcase(orig_claimid);

do i = 'A', 'B', 'C', 'D', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',

    'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z';

  if claim_num = . and (index(claimid,i) > 0) then do;

  claim_use = tranwrd(claimid,i,'0');

  end;

end;

if claim_use = '' then do;

  claim_use = claimid;

end;

drop i;

run;

proc print data = test_id2;

run;

/*///////////////////////////////////////////////////////////*/

I would appreciate any ideas on how to get the code to transform all letters in the string into zeros. Thank you for your hep.

Alex

5 REPLIES 5
data_null__
Jade | Level 19

translate(upcase(claimid),repeat('0',25),'ABCDEFGHIJKLMNIOQRSTUVWXYZ');

avbraga
Calcite | Level 5

data_null_;

that's beautiful my friend. Thank you!

Patrick
Opal | Level 21

Do you know why there are letters in your claim id's? You need a proper explanation for this before transforming such id's or you risk to fold multiple id's into one.

claimed_num=input(prxchange('s/[^\d ]/0/oi',-1,claimid),best32.);

avbraga
Calcite | Level 5

Yeah, Patrick. In theory there shouldn't be letters in this variable, so if it happen I definitely would have to separate those records and inquire as to what's going on.

But for the sake of exercise, data_null_'s response works like a charm and does what I want.

Thanks, guys!

Haikuo
Onyx | Level 15

A PRX approach:

DATA test_id;

INPUT claimid $;

_new=prxchange('s/[a-z]/0/io',-1,claimid);

DATALINES;

551215A

55Cc13c

541215B

abCDE5g

551B159

6512157

57A1215

55Aab5A

5682154

581915c

5519155

;

run;

Haikuo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1292 views
  • 1 like
  • 4 in conversation