BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rigler
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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&...

View solution in original post

6 REPLIES 6
Reeza
Super User

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&...

Rigler
Calcite | Level 5
Great, This was perfect . Thank you!
novinosrin
Tourmaline | Level 20
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;
Rigler
Calcite | Level 5
Another great answer! Thank you!
gamotte
Rhodochrosite | Level 12

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;
Rigler
Calcite | Level 5
This is equally a great solution. Thank you very much!

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!

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
  • 6 replies
  • 4781 views
  • 0 likes
  • 4 in conversation