Substituting part of a str with a number and converting to numeric

[Giles German]

I have another variable called marketcap this is listed as say 100.50m and sometimes it is 1.5bn as seen below. I would like to convert this into numbers. I have done this using the following code in the spirit of having a go and section 9-12 of the FAQ.

560.49m

3.60bn

993.38m

11.74bn

generate marketcapm = subinstr(marketcap, "m", "", .) 
generate maketcapm2 = marketcapm*1000
generate marketcapbn = subinstr(marketcap, "bn", "", .) 
generate marketcapbn2 = marketcapbn*1000000
generate marketcap1 = marketcapm + marketcapbn

[Clyde Schechter]

There are several problems with the proposed code. Because you generate marketcapm and marketcapbn as string variables, you cannot then multiply them by numbers. Even if you fix that, adding the results of marketcapm and marketcapbn would give you the wrong result. I think what you want is this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 marketcap
"560.49m"
"3.60bn" 
"993.38m"
"11.74bn"
end

gen factor = 1e6 if strpos(marketcap, "m")
replace factor = 1e9 if strpos(marketcap, "bn")
destring marketcap, gen(marketcap1) ignore("mbn")
replace marketcap1 = marketcap1 * factor

Note: I have interpreted "m" to mean million (which is 10⁶, not 1000) and "bn" to mean billion, which, at least in the United States, means 10⁹, not 1000000. Perhaps where you are the usage is different: if so, change the numbers in the code creating the variable factor accordingly.

In the future, please use the -dataex- command to show example Stata data, as I have done here. Run -ssc install dataex- to get the command (unless you are running Stata version 15.2, in which case it is already included as part of official Stata). Then run -help dataex- and read the simple instructions for using it. Showing examples with -dataex- is much more helpful than just listing things the way you did: it makes it possible for those who want to help you to quickly and easily create a completely faithful replica of your Stata example with just a simple copy/paste operation. Use -dataex- every time.