subsring on a variable length field

[SVoldrich]

Hi guys - 

I have a Data element A015478

I need to remove the zero.

i figured i can do 
(SUBSTRN(dataelement,1,1)) to get the A separate.

(SUBSTRN(dataelement,3,5)) to get the numbers after the zero

and concatenate them together. 

(SUBSTRN(dataelement,1,1)) ||(SUBSTRN(dataelement,3,5))  hoping to get A15478

the issue is that sometimes the data in that field is A01457 sometimes A02457896  or even A0210501 (a second zero that i need to keep) -- the length of the number portion after the zero is variable.

i was thinking i could use a length statement in that last part... and thinking i may need a put or input to change the type --- but keep getting errors - can anyone help?

What i tried, and the resulting error:

(SUBSTRN(dataelement,1,1))|| (SUBSTRN(dataelement,3,(length((dataelement)-2))))
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Function LENGTH requires a character expression as argument 1.
ERROR: Function SUBSTRN requires a numeric expression as argument 3.

Thanks!

[SVoldrich]

i'm getting closer:
(SUBSTRN(dataelem,1,1))|| (SUBSTRN(dataelem,3,(length(dataelem)-2)))

but when I add it to my where clause, i'm getting 2 rows...

when dataelem is not null and dataelem like 'A%' and ((SUBSTRN(dataelem,1,1))|| (SUBSTRN(dataelem,3,(length(dataelem)-2))))=otherdataelem then otherdataelem

else 'still reviewing'

I get one row with otherdataelem and a second row with still reviewing

[SVoldrich]

ok, that statement works. 
my problem was the other table i was linking to had 2 rows of data one that matched my new concatenated data element, and one that didn't. 

saving this for reference for anyone else stuck!

[JeffMaggio]

Sorry, it took a bit for SAS to boot up for me to test that. I edited my above post to reflect the actual behavior.

TRANWRD will replace using a space for the empty string in the third argument.

If you want to remove the space, you can use TRANSTRN and TRIMN like so:

newString = transtrn(dataelement,'0',trimn(''));

[JeffMaggio]

If all you want to do is remove (all) 0s, you can use TRANWRD.

newString = tranwrd(dataelement,'0','');

that will replace 'A015478' with 'A 15478'.

[Reeza]

TRANWRD and replacement won't work because of this requirement:

or even A0210501 (a second zero that i need to keep) -- the length of the number portion after the zero is variable.

@SVoldrich, you'll need to show more clearly what you have and what you expect. For the example above what would you want? Is it only the second 0 you want to ever replace? If so, that's quite trivial but its hard to tell from your post.

[JeffMaggio · Accepted solution]

Whoops I didn't see that sometimes there are 0s to be kept. I agree that the solution is trivial if it is only the second position 0 that needs to be removed.

@SVoldrichthe substr and substrn functiona do not need the third (final) argument for the length to keep. If that is the case, you can use your original idea without the third argument in the second part:

(SUBSTRN(dataelement,1,1)) ||(SUBSTRN(dataelement,3))

[Reeza]

There's also the SUBSTR() right of operation that could likely be employed but it depends on the OP's exact requirements.

[Tom]

How consist is the data? How many digits could there be?  Why not convert the digits to a number and then back to digits?

cats(char(dataelement,1),input(substr(dataelement,2),32.))