Change mixed variables to character

[tim-hahn]

Thank you very much for taking time to read this post.

I have a Variable ISIN wich is the International Securities Identification Number representing individual securities.It is a 12-character alphanumeric code with a formate like US0231351076 or US00163U1060 etc. 

I would like to transform it to a numeric variable still having the individual representation.

However my code only returns an empty column:

data work.neu;

set work.neu;

isin_num = input(ISIN, best12.);

run;

Thank you very much!

[yabwon]

Is that second "U" valid in the second example? If not then this should help.

data work.neu2;
  set work.neu;
  isin_num = input(compress(ISIN,,"kd"), best12.);
run;

All the best

Bart

[tim-hahn]

Thank you @yabwon for your quick solution.

However, the second U is valid and also the zeros should not be eliminated.

Is it possible to change the Charaters to their decimal place in the alphabet?

So U would become 21 and S would become 12?

[Kurt_Bremser]

And how would you later identify the digits in the number that represent a letter?

Just think of this (taken from https://en.wikipedia.org/wiki/International_Securities_Identification_Number);

TREASURY CORP VICTORIA 5 3/4% 2005-2016: ISIN AU0000XVGZA3

[Shmuel]

Can it be that one person is identified

    by  US00163U1060  and another

    by US00163X1060 - same digits but different alpha characters?

if positive then you can't just use the digits eliminating the alpha characters.

[tim-hahn]

from what I understand I guess this could be possible

[Kurt_Bremser]

"U" or "X" are not digits, so you can't put that into a number without losing information.

Big hint: identification codes (even if fully numeric) should ALWAYS be stored as character.

[tim-hahn]

thank you @Kurt_Bremser .

My problem is I would like to merge two data sets by ISIN however this returns an ERROR: by variable ISIN is not on input dada set. And the resulting new data set has 0 observations.

[Kurt_Bremser]

---

@tim-hahn wrote:

thank you @Kurt_Bremser .

My problem is I would like to merge two data sets by ISIN however this returns an ERROR: by variable ISIN is not on input dada set. And the resulting new data set has 0 observations.

---

That is NOT a problem of numeric vs. character. Instead, the variable does not exist at all.

[tim-hahn]

Oh i thought because it is not numeric it can not be readed

[Kurt_Bremser]

---

@tim-hahn wrote:
Oh i thought because it is not numeric it can not be readed

---

Maxim 3: Know Your Data. This starts (after checking that a dataset exists in the first place) with looking which columns are present in the dataset.

[Shmuel]

is it possible that the variable name is different then ISIN and therefore you got the error message of: variable ISIN is not on input ... ?