topic Re: SUBSTR remove all letter in SAS Programming

SUBSTR remove all letter

Rigler — Mon, 04 Jun 2018 15:16:51 GMT

Hello,

I would like to remove the letters in the dataset below.

Data Have;

input id;

1111-000001KK
1111-000002IM
1111-000003IM
1111-000011IM
1111-000111IM
1111-000221IM
1111-000331IM
1111-000441IM
1111-000002IM
IM-1111-000644
IM-1111-001264
IM-1111-001613
IM-1111-001630
IM-1111-001632
IM-2222-001825
IM-1111-001931
IM-1111-002029
IM-1111-002270
IM-3333-002387;

run;

Can anyone guide me as how to accomplish this task so the id variable has only numbers, as in Dataset Want?

Data want;

1111-000001
1111-000002
1111-000003
1111-000011
1111-000111
1111-000221
1111-000331
1111-000441
1111-000002
1111-000644
1111-001264
1111-001613
1111-001630
1111-001632
2222-001825
1111-001931
1111-002029
1111-002270
3333-002387

Thank you.

Re: SUBSTR remove all letter

Reeza — Mon, 04 Jun 2018 15:23:45 GMT

Use the COMPRESS() function instead of SUBSTR.

That will remove all the letters, but you'll have to check if you end up with a leading or trailing - that needs to be removed.

You can use SUBSTR() for that, along with CHAR() to check the first/last character.

x = compress(string, , 'a');

List of SAS functions are available here:

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm&docsetVersion=9.4&locale=en

Re: SUBSTR remove all letter

novinosrin — Mon, 04 Jun 2018 15:24:17 GMT

Data Have;

input id $20.;
cards;
1111-000001KK
1111-000002IM
1111-000003IM
1111-000011IM
1111-000111IM
1111-000221IM
1111-000331IM
1111-000441IM
1111-000002IM
IM-1111-000644
IM-1111-001264
IM-1111-001613
IM-1111-001630
IM-1111-001632
IM-2222-001825
IM-1111-001931
IM-1111-002029
IM-1111-002270
IM-3333-002387
;

run;

data want;
set have;
t=compress(id,' ','a');
id=ifc(length(t)>11,substr(t,2),t);
drop t;
run;

Re: SUBSTR remove all letter

gamotte — Mon, 04 Jun 2018 15:30:09 GMT

Hello,

Here is one solution

Data Have;
input id $20.;
id=prxchange("s/-?[A-Z]-?//",-1,id);

cards;
1111-000001KK
1111-000002IM
1111-000003IM
1111-000011IM
1111-000111IM
1111-000221IM
1111-000331IM
1111-000441IM
1111-000002IM
IM-1111-000644
IM-1111-001264
IM-1111-001613
IM-1111-001630
IM-1111-001632
IM-2222-001825
IM-1111-001931
IM-1111-002029
IM-1111-002270
IM-3333-002387
;
run;

Re: SUBSTR remove all letter

Rigler — Mon, 04 Jun 2018 16:09:26 GMT

Great, This was perfect . Thank you!

Re: SUBSTR remove all letter

Rigler — Mon, 04 Jun 2018 16:09:56 GMT

Another great answer! Thank you!

Re: SUBSTR remove all letter

Rigler — Mon, 04 Jun 2018 16:12:12 GMT

This is equally a great solution. Thank you very much!