topic Re: subsring on a variable length field in SAS Programming

subsring on a variable length field

SVoldrich — Thu, 16 Jan 2020 23:46:01 GMT

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!

Re: subsring on a variable length field

SVoldrich — Thu, 16 Jan 2020 23:51:07 GMT

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

Re: subsring on a variable length field

JeffMaggio — Fri, 17 Jan 2020 00:17:09 GMT

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

Re: subsring on a variable length field

SVoldrich — Fri, 17 Jan 2020 00:07:02 GMT

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!

Re: subsring on a variable length field

JeffMaggio — Fri, 17 Jan 2020 00:21:29 GMT

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(''));

Re: subsring on a variable length field

Reeza — Fri, 17 Jan 2020 00:25:09 GMT

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.

Re: subsring on a variable length field

JeffMaggio — Fri, 17 Jan 2020 01:25:44 GMT

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))

Re: subsring on a variable length field

Reeza — Fri, 17 Jan 2020 02:11:56 GMT

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

Re: subsring on a variable length field

Tom — Fri, 17 Jan 2020 04:34:21 GMT

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