Convert character variable with leading zeroes into numeric variable

[Justin9]

I have a character variable, 'ACCOUNT_NUMBER' (format $30, length 30), and want to convert it into a new numeric variable called 'FINAL_NUMBER' with a length of 20 (I know the maximum length for a numeric variable is 8, but my client has specified that the length of the numeric variable should be 20, so maybe that means 20 for format?). The 'ACCOUNT_NUMBER' variable has leading zeroes at the front e.g. 001210003726, 001210015487, 001223582462 etc., so I am not getting the desired outcome with the  'FINAL_NUMBER' (i.e.  "FINAL_NUMBER" is not matching what was included in the original 'ACCOUNT_NUMBER' character variable). Please can someone help to edit my code or suggest an alternative that I could use in my data step?

data Complete_dataset;
   set test;
   FINAL_NUMBER=input(ACCOUNT_NUMBER,12.);
   format FINAL_NUMBER z20.;
run;

[SASKiwi]

You can't convert 30 character long account numbers in SAS to numeric. SAS numbers can only hold 15 digits accurately. Do you just want to remove the leading zeroes? What is the maximum number of leading zeroes?

[Justin9]

Thanks for your reply. I want my new numeric variable (requested as length 20), 'FINAL_NUMBER', to exactly match what was shown in my original character variable (length 30), 'ACCOUNT_NUMBER'.

For example, my character variable, 'ACCOUNT_NUMBER' has the following: 001210003726, 001210015487 and 001223582462.
As a result, I want my new numeric variable, 'FINAL_NUMBER' to also look the same with 001210003726, 001210015487 and 001223582462 (these are three examples in my dataset).

Is there any way to do this? From a first glance, all accounts have two 0s at the front and then 10 digits after, so 12 digits in total.

[SASKiwi]

I would strongly advise against putting account numbers into numeric variables. Unless you are doing calculations on account numbers, which is highly unlikely, they are best left as character. That way there is no chance of losing precision (that is digits being scrambled) when you have over 15 digits. If you have have any account numbers with more than 15 digits, like credit card numbers, and you convert them to numeric any digits beyond 15 will get randomly scrambled as they cannot be held accurately in SAS's 8-byte numeric variables.

EDIT: BTW, you can't store leading zeroes in a numeric variable, you can only display leading zeroes using the "Z" format. That's another good reason not to convert.    

[Justin9]

It's a request from someone else to specifically have the new variable as a numeric variable and length 20, so is there any change to the code I wrote in my original post that I could make to test whether 'FINAL_NUMBER' could match what my original character variable 'ACCOUNT_NUMBER' was showing?

[pink_poodle]

Could you please double check with the client that they really need a numeric variable, not just a character variable with a length of 20?

[Justin9]

Yes, I have checked and it definitely needs to be numeric and length of 20 for their processing and matching process. Is there any way of making creating a new numeric variable called 'FINAL_NUMBER' that matches what was included in the 'ACCOUNT_NUMBER' character variable (see example of some account numbers in my earlier posts)? Or at least numeric to match and then I can try to negotiate about the length of the new 'FINAL_NUMBER' variable?

[SASKiwi]

Try running this program as a test:

data _null_;
 account_number = '001210003726';
 final_number = input(account_number, 12.);
 if account_number = final_number then Match_Flag = 'Y';
 if account_number = put(final_number, z12.) then Match_Flag2 = 'Y';
 put _all_;
run;

It will give you this SAS log:

34         data _null_;
35          account_number = '001210003726';
36          final_number = input(account_number, 12.);
37          if account_number = final_number then Match_Flag = 'Y';
38          if account_number = put(final_number, z12.) then Match_Flag2 = 'Y';
39          put _all_;
40         run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      37:5   
account_number=001210003726 final_number=1210003726 Match_Flag=Y Match_Flag2=Y _ERROR_=0 _N_=1
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Note that to compare your two account variables SAS must convert the variable type of one to the other. In the first IF statement I've let SAS do the conversion and it has chosen to convert the character variable to numeric. In the second IF statement I've explicitly converted the numeric variable to character so the two values can be compared. Both report the variables are 'equal', but conversion has to happen for the comparison to be made.

Are all of your account numbers 12 digits long? 

[Justin9]

Thanks a lot for your message! Because my dataset has about 100,000 observations, I've just used your final_number = input(account_number, 12.); line. My final_number observation matches the majority of the original account_number, with the only difference being that final_number omits the two leading 0s. Have I missed something in the code?

Example: After running the code, here is one observation
account_number = 001210003726
final_number = 1210003726

data Complete_dataset;
set test;
FINAL_NUMBER=input(ACCOUNT_NUMBER,12.);
run;

[SASKiwi]

Please remember that I said you can't store leading zeroes in a numeric variable, only display them that way? Add a FORMAT statement to display leading zeroes which only applies when reporting:

data Complete_dataset;
set test;
FINAL_NUMBER=input(ACCOUNT_NUMBER,12.);
format FINAL_NUMBER z12.;
run;

[pink_poodle]

Please check out a similar post here:
https://communities.sas.com/t5/SAS-Programming/Adding-leading-Zeros-to-Numeric-Variable/td-p/90932/p...

[mkeintz]

Putting aside the general inadvisability of storing ID's as numeric variables, what your client wants simply cannot be done with accuracy. Per the program below, the maximum consecutive exact integer that can be stored in an 8-byte numeric SAS variable is =9,007,199,254,740,992 - a 16-digit number.  There will be integers above that which can not be accurately stored in a floating point double word number (i.e. in a SAS numeric variable of length 8):  

data _null_;
  x=constant('exactint',8);
  put x=z20. / x=comma24.0;
run;

Above the number, there will be holes.  For example,

Above that number, you will able to accurately store only even numbers.  Odd numbers will be rounded to even.

Above twice that number every value will be rounded to a number divisible by four.

Above four times that number, ... rounding to a number divisible by eight (see code below for a demonstration):

data _null_;
  x=constant('exactint',8);
  x4=4*x;
  do i=0 to 9;
    y=x4+i;
    put i=  y=comma24.0;
  end;
run;

[mkeintz]

Maybe your client is trying to avoid typing in leading zeroes to generate a twenty-character value when the numeric equivalent only needs 9 digits.  If so, generate a macro (call it LZ) that prepends sufficient zeroes to the value entered to make it work.

%macro lz (num)/ des="generate 20 digit character value with leading zeroes" ;
  %sysfunc(repeat(0,%eval(20-%length(&num)-1)))&num
%mend;

data want;
  set have;
  if id="%lz(12345677)" then .... ;
  ...
run;