Need help to convert string to numeric number (tried many commands but not work yet)

[Waewnet Sukkasem]

Dear all,

I imported data from excel and the column HH_2010, HH_2011,............. HH_2021 are supposed to be in numeric number with 2 digits but once I imported they show as string (Type str17).

Country	HH_2010	HH_2011	HH_2012	......	HH_2021
Albania	78.10933694727119	78.29943473286669	80.02937366162661		80.02937366162661
American Samoa	53.50191201900771
Algeria	53.50191201900771
Angola	53.50191201900771

I tried destring but it said "contains non-numeric characters". Then, I used encode and it shows the blue color with 15 decimals.

Can you please help me with the way to convert from string to 2 digits numeric easily?

Thanks so much in advance.

[Clyde Schechter]

You absolutely should not use -encode- on this. It is not an alternative to -destring-. It does something completely different, and in your context, useless, even dangerous.

You need to find out why the variable was imported as a string to begin with: what are the non-numeric characters it contains.

Let's go with the obvious first: your screenshot shows that many of the values in these variables are shown as "..". Right there you have a problem. You can get rid of that easily with:

foreach v of varlist HH* {
    replace `v' = "" if `v' == ".."
}

and then try your -destring- again.

If -destring- still balks at making the conversion, then there must be other entries that are not proper numbers. To find them:

gen byte problem = 0
foreach v of varlist HH20* {
    replace problem = 1 if missing(real(`v'))
}
browse country HH* if problem

This will show you the values of the HH* variables in any observation where one or more of the HH* variables contains non-numeric characters. You then have to either use some -replace- statements to get rid of those non-numeric characters and then -destring-, or put those characters into -destring-'s -ignore()- option so that -destring- will overlook them.

Now, sometimes when you run the code above, everything you see looks, to your eyes, like normal numbers. In that case, the offending non-numeric characters are probably non-printing characters. To find them, run:

chartab HH*

You will get a list of all of the characters (including ones the eye can't see) in these variables along with their decimal and hexadecimal codes and a description of what they are. You can then use -replace- commands to eliminate all of the characters that don't belong in numbers. -chartab- is written by Robert Picard and is available from SSC.

[Waewnet Sukkasem]

Dear Clyde,

OMG, that works. Thank you very much for "for each" command. I'm not good at for each. This is very helpful. Many thanks again.