topic Re: Change mixed variables to character in SAS Programming

Change mixed variables to character

tim-hahn — Thu, 07 May 2020 10:45:42 GMT

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!

Re: Change mixed variables to character

yabwon — Thu, 07 May 2020 11:11:41 GMT

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

Re: Change mixed variables to character

Shmuel — Thu, 07 May 2020 11:40:40 GMT

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.

Re: Change mixed variables to character

tim-hahn — Thu, 07 May 2020 11:43:17 GMT

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?

Re: Change mixed variables to character

tim-hahn — Thu, 07 May 2020 11:48:32 GMT

from what I understand I guess this could be possible

Re: Change mixed variables to character

Kurt_Bremser — Thu, 07 May 2020 11:59:23 GMT

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

Re: Change mixed variables to character

tim-hahn — Thu, 07 May 2020 12:04:32 GMT

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.

Re: Change mixed variables to character

Kurt_Bremser — Thu, 07 May 2020 12:06:09 GMT

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

Re: Change mixed variables to character

tim-hahn — Thu, 07 May 2020 12:07:12 GMT

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

Re: Change mixed variables to character

Kurt_Bremser — Thu, 07 May 2020 12:11:29 GMT

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

Re: Change mixed variables to character

Shmuel — Thu, 07 May 2020 12:16:53 GMT

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

Re: Change mixed variables to character

Kurt_Bremser — Thu, 07 May 2020 12:20:10 GMT

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