Convert date variable to scale variable

[Inaamul Haq]

Hi all
I imported an excel dataset to Stata 15. Unfortunately a scale variable (Body Mass Index measured up to 2 decimal places) had a wrongly placed date entry in one observation. As a result Stata converted the whole variable "bmi" as a date variable displaying it in the %td format. I need to convert this "bmi" to the number format with 2 decimal places. Here is the example data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double bmi
 -21893.3
 -21893.9
 -21892.5
 -21895.2
   -21892
 -21895.1
 -21892.8
 -21896.3
 -21895.7
   -21894
 -21893.5
 -21894.4
 -21892.7
 -21894.1
 -21895.2
 -21893.7
 -21886.5
 -21895.3
 -21896.5
 -21893.8
-21893.75
 -21896.3
   -21892
 -21891.9
 -21893.2
 -21891.8
 -21893.5
 -21895.3
 -21887.3
 -21887.5
 -21891.8
 -21889.9
 -21896.1
 -21893.5
   -21892
 -21894.1
 -21894.9
   -21896
 -21895.1
 -21885.2
   -21885
 -21890.9
 -21892.2
 -21891.9
 -21889.7
 -21887.8
 -21893.2
   -21893
   -21882
-21889.85
 -21892.3
-21896.25
-21894.35
-21891.88
 -21890.1
 -21889.8
 -21892.7
-21895.11
 -21890.9
 -21893.1
 -21893.9
-21895.11
-21896.22
 -21889.9
-21891.55
 -21892.2
-21890.46
 -21886.4
 -21891.5
 -21894.7
-21895.22
   -21896
 -21889.6
 -21888.8
   -21891
   -21884
 -21884.6
-21895.58
-21890.38
 -21890.9
 -21892.2
-21895.56
-21895.13
-21892.36
-21892.87
-21896.25
-21894.04
-21894.88
-21893.56
-21896.93
-21892.91
-21891.24
-21896.68
-21894.79
-21895.11
-21893.24
-21893.63
-21885.35
-21897.02
-21895.78
end
format %td bmi

[Nick Cox]

There may be a clever way to reverse this but it seems to me that BMIs of zero are unlikely in your data. One way to solve this might if you tell us the spurious date so that Stata’s conversion can be re-created. Otherwise the most pessimistic solution is to edit the original file and try again. Or insist on import as string and fix the miscreant value in Stata before a destring.

[Nick Cox]

bmi - mdy(1, 1, 1900)

may be what you seek, but that's just a guess.

[Inaamul Haq]

Thanks, Nick Cox.
The code you suggested, somehow, displayed bmi values which were the original bmi minus 1. So I used the code below and it worked perfectly.

gen bminew = (bmi + 1) - mdy(1, 1, 1900)