topic Re: Removing specific characters from the beginning and end of a variable in SAS Programming

Removing specific characters from the beginning and end of a variable

deleted_user — Tue, 04 Jan 2011 22:56:48 GMT

I have IDs in file A as follows:
p704987-1
p789094-22
p-3455-3

In file B I have IDs as follows:
704987
789094
3455

They are same ID, but in file A, a letter has been added to the beginning as well as a (-) and number following that are not needed.

I need to remove the p at the beginning of the ID variable in file A and remove the (-) and any following numbers after the (-) for ID variable in file A as well so I can merge the data from file A with file B using the ID variable from both files, but I cannot seem to figure out the right code. Any help is appreciated. Thank you. Message was edited by: JHale

Re: Removing specific characters from the beginning and end of a variable

Cynthia_sas — Tue, 04 Jan 2011 23:17:55 GMT

Hi:
In file A the IDs must be character strings. What about file B? Are the IDs numeric or character variables???

If the data is exactly as you describe (that the IDs in file A always start with a 'p' or a 'p-'), then I'd probably recommend that you investigate the SCAN function. What remains to be determined is whether you will be doing a character to character comparison between the IDs in file A and file B or whether you will be doing a numeric to numeric comparison between the IDs.

cynthia

Re: Removing specific characters from the beginning and end of a variable

art297 — Wed, 05 Jan 2011 00:56:41 GMT

Regular expressions would probably be the easiest way to solve the problem, but you could also probably do it with something like:

data want;
set have;
id=substr(id,1,findc(id,'-',-length(id))-1);
id=substr(id,findc(id,,'d'));
run;

HTH,
Art

Re: Removing specific characters from the beginning and end of a variable

Ksharp — Wed, 05 Jan 2011 02:10:04 GMT

Hi.
Just as Cynthia said.
Use Function scan() would be more simpler.

[pre]
data temp;
input id : $20.;
id=scan(id,-2,'-pP');
put id=;
cards;
p704987-1
p789094-22
p-3455-3
run;

[/pre]

Ksharp

Re: Removing specific characters from the beginning and end of a variable

art297 — Wed, 05 Jan 2011 03:49:09 GMT

As long as the prefix is always a 'p' I totally agree.

Art

Re: Removing specific characters from the beginning and end of a variable

Cynthia_sas — Wed, 05 Jan 2011 06:14:35 GMT

Hi:
Actually, the prefix can be either - or p or p- or -p or not there at all -- SCAN ignores leading delimiters so this data:
[pre]
p704987-1
p789094-22
p-3455-3
-1234-p44
34567p-55
-p98765-66
[/pre]

Can all use this program:
[pre]
data char_id;
length char_id $12;
infile datalines;
input char_id $;
new_char_id = scan(char_id,1,'p-');
new_num_id = input(new_char_id,best8.);
put _n_= char_id= new_char_id= new_num_id= ;
return;
datalines;
p704987-1
p789094-22
p-3455-3
-1234-p44
34567p-55
-p98765-66
;
run;
[/pre]

With these results in the log:
[pre]
_N_=1 char_id=p704987-1 new_char_id=704987 new_num_id=704987
_N_=2 char_id=p789094-22 new_char_id=789094 new_num_id=789094
_N_=3 char_id=p-3455-3 new_char_id=3455 new_num_id=3455
_N_=4 char_id=-1234-p44 new_char_id=1234 new_num_id=1234
_N_=5 char_id=34567p-55 new_char_id=34567 new_num_id=34567
_N_=6 char_id=-p98765-66 new_char_id=98765 new_num_id=98765
[/pre]

SCAN is pretty cool for a low-tech, non-PRX function.

cynthia

Re: Removing specific characters from the beginning and end of a variable

Ksharp — Wed, 05 Jan 2011 07:49:57 GMT

Hi.
I scan it from right not left,So as long as the right character is to looks like '-23' '-p23',that will be OK.

Ksharp

Re: Removing specific characters from the beginning and end of a variable

deleted_user — Wed, 05 Jan 2011 14:44:11 GMT

Thank you so much! This worked perfectly.

Re: Removing specific characters from the beginning and end of a variable

deleted_user — Wed, 05 Jan 2011 14:44:42 GMT

Thank you to everyone for your advice and help. Much appreciation.

Re: Removing specific characters from the beginning and end of a variable

deleted_user — Thu, 06 Jan 2011 11:46:19 GMT

Hello,

If your string begins with other letters than p maybe this solution will fit:

data test;
input x $40.;
y=compress(substr(x,1,find(trim(x),'-',-length(trim(x)))), ,'kd');
datalines;
p704987-1
p789094-22
p-03455-3
-1234-p44
34567p-55
-p98765-66
;

Marius