topic Re: Drop leading zeros from character variable in SAS Enterprise Guide

Drop leading zeros from character variable

RPYee — Fri, 09 Mar 2018 18:15:15 GMT

I have an employee number field that can be anywhere from 5 to 8 alpha-characters. The field is 10 characters wide and the employee number is prefaced with leading zeros to fill the field. There may or may not be zeros in the actual employee number. I use only the EG interface to build queries and design summary reports. How can I add a computed field to trim the employee number of any leading zeros?

Thanks, in advance for your assistance!

Rita Yee

Project Engineer

FedEx Express

Re: Drop leading zeros from character variable

Kurt_Bremser — Fri, 09 Mar 2018 18:29:09 GMT

Are the employee numbers actually numbers, or can they contain any non-digit characters?

Re: Drop leading zeros from character variable

mkeintz — Sat, 10 Mar 2018 01:45:11 GMT

If you know that zeroes never occur except as left-hand leading characters, then the translate function works:

  x=translate(x,' ','0');

Note the 2nd and 3rd arguments of translate may not be in the intutitively expected order. TRANSLATE converts the 3rd character (0) to the 2nd character (' ').

Added note. If you can have non-leading zeroes that you want to preserve:

  do while (x=:'0');
    x=substr(x,2);
  end;
  x=right(x);

Re: Drop leading zeros from character variable

Patrick — Fri, 09 Mar 2018 22:29:33 GMT

If your source variable (let's call it emp_id) is numeric then just change the format on it - i.e. assign format BEST32.

If emp_id is character and you're sure it only contains digits then use an informat to convert the string to a number:

emp_id_want=input(emp_id,best32.);

If emp_id can contain alphanumeric characters then code like below should do the trick:

emp_id_want=prxchange('s/^\s*0*//oi',1,emp_id);

Re: Drop leading zeros from character variable

TomKari — Sat, 10 Mar 2018 15:20:00 GMT

Hi, Rita

Glad to see you're persevering with SAS!

To put the advice in the above posts into an EG context, create a new computed column, and make it an advanced expression.

You can use all of the functions described above in the advanced expression. Here's another way, assuming that your fields can only contain digits. I'm using EmpID as the field name; just change as needed, and drop this into the "Enter an expression" box:

right(put(input(EmpID, best10.),best10.))

It's first using the input function to convert your character field to a number, then using the put function to convert the number back to character (the best10. format will put in leading blanks.) Then the right function shifts the non-blank characters to the right of the field.

Tom

Re: Drop leading zeros from character variable

Tom — Sat, 10 Mar 2018 15:39:51 GMT

You can use the VERIFY() function to find the location of the first non-zero character. You can then use that information in the SUBSTR() function.

data test;
  input str $20. ;
  new_str = substr(str,verify(str,'0'));
  put (_all_) (= $quote.);
cards;
123
00ABC
;

Re: Drop leading zeros from character variable

RPYee — Mon, 12 Mar 2018 19:44:34 GMT

They are only numbers.

Re: Drop leading zeros from character variable

Kurt_Bremser — Mon, 12 Mar 2018 23:07:03 GMT

Then do

empnum = strip(put(input(empnum,best.),best.));

Re: Drop leading zeros from character variable

Tom — Tue, 13 Mar 2018 03:25:34 GMT

Better to use 32. as the INFORMAT and 32. as the FORMAT.
The BEST format will default to 12 characters and larger integers will be presented in scientific format. Since the values are by definition integers then just tell SAS that. 32 is the maximum width.
Also for informats there is no real BEST informat. If you use BEST as an informat is just an **alias** for the normal NN. informat. Calling it BEST, as if it did something different, will just confuses novice SAS users into thinking that it is somehow going to do something different than the normal numeric informat.

Re: Drop leading zeros from character variable

RPYee — Tue, 10 Apr 2018 14:53:56 GMT

First solution I tried and it worked like a charm. Thanks!!!!

Re: Drop leading zeros from character variable

jquon126 — Fri, 03 May 2019 15:27:20 GMT

This thread has been helpful. I'm looking for something similar but my values contain some non digit characters. How can I modify your solution to fit that criteria?

Thanks in advance.

Re: Drop leading zeros from character variable

Kurt_Bremser — Sat, 04 May 2019 03:52:31 GMT

Please supply some examples for your character values, and what you expect as results.

Re: Drop leading zeros from character variable

Tom — Sat, 04 May 2019 16:35:10 GMT

@jquon126 wrote:

This thread has been helpful. I'm looking for something similar but my values contain some non digit characters. How can I modify your solution to fit that criteria?

Thanks in advance.

Then use one of the other answers that can work on strings that are not representations of numbers.

Re: Drop leading zeros from character variable

JSJ — Wed, 10 Jun 2020 22:54:43 GMT

In SAS EG, create a calculated field--

For Char to numeric

INPUT(t1.'Ent Id Number'n,Z10.)

Convert numeric Entity ID(for example, imported from a spreadsheet) without leading zeroes to 10 Char with leading zeroes.

From Numeric to Char

PUT(t1.'Entity ID'n,Z10.)

Best of luck!