How to convert the whole dataset from string to numeric data ?

[ALKEBSEE RADWAN]

Hello everyone,
I know that sometimes I ask strange question. But, I have a dataset that consists of all string variables and there are over 100 variables.
Can anyone explain whether we can convert all variables to numeric data at once using a command?

thank you so much.

[Clyde Schechter]

destring _all, replace

Now, it may be that some of the variables in the data set are not suitable for conversion to numeric. For example, there may be some values that contain extraneous characters, or where missing values were coded as "N/A" etc. The -destring- command above will deal with all of the variables that can be converted. For the others, it will tell you that it cannot. You will then have to review those variables to see what kind of non-numeric content they have, and how you want to deal with that. The -ignore()- option in -destring- is helpful for dealing with simple things such as a variable that is numeric except for beginning with $, or that contains commas separating three-digit number groups. More complicated problems may require problem-specific coding.

[ALKEBSEE RADWAN]

destring _all, replace

Now, it may be that some of the variables in the data set are not suitable for conversion to numeric. For example, there may be some values that contain extraneous characters, or where missing values were coded as "N/A" etc. The -destring- command above will deal with all of the variables that can be converted. For the others, it will tell you that it cannot. You will then have to review those variables to see what kind of non-numeric content they have, and how you want to deal with that. The -ignore()- option in -destring- is helpful for dealing with simple things such as a variable that is numeric except for beginning with $, or that contains commas separating three-digit number groups. More complicated problems may require problem-specific coding.

thank you so much Clyde Schechter
Actually I dont have missing values.
however, let me try this command if I face an issue I will inform you
once again thank you so much.

[ALKEBSEE RADWAN]

@Clyde Schechter I have tried this command by it does not work.
I have run it but the data still described as follow.

describe

Contains data
  obs:         9,561                          
 vars:           368                          
 size:    37,393,071                          
-------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------------------------------
code            str14   %14s                  code
com_name        str91   %91s                  COm_name
r365            str7    %9s                   R365
r364            str11   %11s                  R364
r363            str11   %11s                  R363
r362            str11   %11s                  R362
r361            str11   %11s                  R361
r360            str11   %11s                  R360
r359            str7    %9s                   R359
r358            str11   %11s                  R358
r357            str11   %11s                  R357
r356            str11   %11s                  R356
r355            str11   %11s                  R355
r354            str11   %11s                  R354
r353            str11   %11s                  R353
r352            str7    %9s                   R352
r351            str11   %11s                  R351
r350            str11   %11s                  R350
r349            str11   %11s                  R349
r348            str11   %11s                  R348
r347            str11   %11s                  R347
r346            str11   %11s                  R346
r345            str7    %9s                   R345
r344            str11   %11s                  R344
r343            str11   %11s                  R343
r342            str11   %11s                  R342
r341            str11   %11s                  R341
r340            str11   %11s                  R340
r339            str11   %11s                  R339
r338            str7    %9s                   R338
r337            str11   %11s                  R337
r336            str11   %11s                  R336
r335            str11   %11s                  R335
r334            str11   %11s                  R334
r333            str11   %11s                  R333
r332            str11   %11s                  R332
r331            str7    %9s                   R331
r330            str11   %11s                  R330
r329            str11   %11s                  R329
r328            str11   %11s                  R328
r327            str11   %11s                  R327
r326            str11   %11s                  R326
r325            str11   %11s                  R325

[William Lisowski]

You need to show the destring command you ran and the messages it gave you, and you need to show a sample of your data. Then we can address the problems you are having.

Without that, we can only guess, or try to address every possible problem you might have. Neither is an appealing way to spend a nice afternoon.

[ALKEBSEE RADWAN]

You need to show the destring command you ran and the messages it gave you, and you need to show a sample of your data. Then we can address the problems you are having.

Without that, we can only guess, or try to address every possible problem you might have. Neither is an appealing way to spend a nice afternoon.

Thank you for replying
fist of all this is a sample of my data

 +-----------------------------------------------------------------------------------------------------------------+
 24. |      code  |                                 com_name  |  r365  |         r364  |         r363  |         r362  |
     |    8955.T  |       Japan Prime Realty Investment Corp  |     0  |            0  |  0.016874276  |  0.024490268  |
     |-------------------------------------------------------------------------------------------------+---------------|
     |        r361 | r360 | r359 |        r358 |        r357 |        r356 |        r355 |        r354 | r353  | r352  |
     | 0.031833326 |    0 |    0 | 0.001537752 | 0.000718891 | 0.009720288 | 0.013770425 | 0.006057471 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r351 |        r350 |        r349 |        r348 |        r347 | r346 | r345 |        r344  |        r343  |
     | 0.003539162 | 0.003672207 | 0.012197292 | 0.000965769 | 0.020896102 |    0 |    0 | 0.014108667  | 0.003210926  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r342 |        r341 |        r340 | r339 | r338 |        r337 |        r336 |        r335  |        r334  |
     | 0.028717295 | 0.008261542 |  0.01302871 |    0 |    0 | 0.014146093 | 0.021171306 | 0.015418851  | 0.029784226  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r333 | r332 | r331 |        r330 |        r329 |        r328 |        r327 |        r326 | r325  | r324  |
     | 0.016338914 |    0 |    0 | 0.015887483 | 0.003542989 | 0.016151483 | 0.000573758 |           0 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r323 |        r322 |        r321 |        r320 |        r319 | r318 | r317 |        r316  |        r315  |
     | 0.006770025 | 0.010137645 | 0.007326679 |  0.00780742 | 0.025216897 |    0 |    0 | 0.045914287  | 0.016144481  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r314 |        r313 |        r312 | r311 | r310 |        r309 |        r308 |        r307  |        r306  |
     | 0.027594175 | 0.011932919 | 0.005062908 |    0 |    0 | 0.011273383 | 0.027552536 | 0.031083905  |           0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r305 | r304 | r303 |        r302 |        r301 |        r300 |        r299 |        r298 | r297  | r296  |
     | 0.008456748 |    0 |    0 | 0.026843066 | 0.001082261 | 0.025926213 | 0.003716611 | 0.018325512 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r295 |        r294 |        r293 |        r292 |        r291 | r290 | r289 |        r288  |        r287  |
     | 0.017338652 | 0.010530636 |  0.00597779 | 0.030095305 | 0.019240987 |    0 |    0 | 0.021419218  | 0.004885007  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r286 |        r285 |        r284 | r283 | r282 |        r281 |        r280 |        r279  |        r278  |
     | 0.012280339 | 0.010507979 | 0.014052592 |    0 |    0 | 0.003538397 | 0.007949244 | 0.006195999  | 0.003901676  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r277 | r276 | r275 |        r274 |        r273 |        r272 |        r271 |        r270 | r269  | r268  |
     | 0.023102466 |    0 |    0 | 0.005705947 |           0 | 0.005844635 | 0.013597278 | 0.022040791 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r267 |        r266 |        r265 |        r264 |        r263 | r262 | r261 |        r260  |        r259  |
     | 0.010395688 | 0.032333269 | 0.015931766 |           0 |           0 |    0 |    0 | 0.018946353  | 0.014456667  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r258 |        r257 |        r256 | r255 | r254 |        r253 |        r252 |        r251  |        r250  |
     | 0.012509816 | 0.023386948 | 0.007380036 |    0 |    0 | 0.022604383 | 0.005091958 |  0.02049525  |  0.02082145  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r249 | r248 | r247 |        r246 |        r245 |        r244 |        r243 |        r242 | r241  | r240  |
     | 0.020729262 |    0 |    0 | 0.008309444 | 0.032349206 | 0.005782901 | 0.026181131 |  0.00261985 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r239 |        r238 |        r237 |        r236 |        r235 | r234 | r233 |        r232  |        r231  |
     | 0.014465114 | 0.002550478 | 0.008325207 | 0.026782853 | 0.006747397 |    0 |    0 | 0.038451557  | 0.013800225  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r230 |        r229 |        r228 | r227 | r226 |        r225 |        r224 |        r223  |        r222  |
     | 0.024490865 | 0.013124669 | 0.036851615 |    0 |    0 | 0.020800164 | 0.038271104 | 0.003850488  | 0.001063895  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r221 | r220 | r219 |        r218 |        r217 |        r216 |        r215 |        r214 | r213  | r212  |
     |           0 |    0 |    0 | 0.015770049 | 0.029181884 | 0.032236902 | 0.024642535 | 0.014048459 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r211 |        r210 |        r209 |        r208 |        r207 | r206 | r205 |        r204  |        r203  |
     |  0.01652673 | 0.025658741 | 0.014119296 | 0.030618185 | 0.014844436 |    0 |    0 |           0  |           0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r202 |        r201 |        r200 | r199 | r198 |        r197 |        r196 |        r195  |        r194  |
     | 0.004925237 | 0.005333843 | 0.009419127 |    0 |    0 | 0.011182317 | 0.012647099 | 0.009882397  | 0.016751212  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r193 | r192 | r191 |        r190 |        r189 |        r188 |        r187 |        r186 | r185  | r184  |
     | 0.020957754 |    0 |    0 | 0.015295609 | 0.004388285 | 0.009048076 | 0.014496821 | 0.021009243 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r183 |        r182 |        r181 |        r180 |        r179 | r178 | r177 |        r176  |        r175  |
     | 0.012727599 | 0.009103539 | 0.016709136 | 0.017635138 | 0.029872263 |    0 |    0 |  0.01653386  | 0.032549492  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r174 |        r173 |        r172 | r171 | r170 |        r169 |        r168 |        r167  |        r166  |
     | 0.010276009 |  0.00118225 | 0.022727273 |    0 |    0 | 0.068867301 | 0.006168766 | 0.001528357  | 0.065415099  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r165 | r164 | r163 |        r162 |        r161 |        r160 |        r159 |        r158 | r157  | r156  |
     | 0.083809156 |    0 |    0 | 0.001187614 | 0.056874667 | 0.003135941 | 0.003752853 | 0.056230487 |    0  |    0  |
     |-------------+-------------+-------------+-------------+-------------+-------------+-----------------------------|
     |        r155 |        r154 |        r153 |        r152 |        r151 | r150 | r149 |        r148  |        r147  |
     | 0.007303203 | 0.030962406 | 0.024725717 | 0.017760199 | 0.032767196 |    0 |    0 | 0.035220855  | 0.003985612  |
     |-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------|
     |        r146 |        r145 |        r144 | r143 | r142 |        r141 |        r140 |        r139  |        r138  |
     | 0.015531848 | 0.014337984 | 0.018688486 |    0 |    0 |           0 | 0.000930146 | 0.017065814  | 0.010192076  |
     |-------------+-------------+-------------------------------------------------------------------------------------|
     |        r137 | r136 | r135 |        r134  |        r133  |        r132  |        r131  |        r130  |    r129  |
     | 0.023002137 |    0 |    0 |  0.04319907  | 0.013089337  | 0.063263565  | 0.024389443  |  0.02549636  |       0  |
     |-----------------------------------------------------------------------------------------------------------------|
     |    r128 |        r127 |        r126 |        r125 |        r124 |        r123 |    r122 |    r121 |        r120 |
     |       0 | 0.065938657 | 0.008171245 |           0 |           0 |           0 |       0 |       0 |   0.0021176 |
     |-----------------------------------------------------------------------------------------------------------------|
     |        r119  |        r118  |        r117  |        r116  |    r115  |    r114  |         r113  |         r112  |
     | 0.001459447  |           0  | 0.032798743  | 0.017153368  |       0  |       0  |  0.020622046  |  0.033463928  |
     |--------------+--------------+--------------+------------------------------------+---------------+---------------|
     |        r111  |        r110  |        r109  |    r108  |    r107  |        r106  |         r105  |         r104  |
     | 0.011271736  | 0.050804807  | 0.010343282  |       0  |       0  | 0.016356568  |  0.009110923  |  0.021345298  |
     |--------------+--------------+------------------------------------+--------------+---------------+---------------|
     |        r103  |        r102  |    r101  |    r100  |         r99  |         r98  |          r97  |          r96  |
     | 0.014365427  |  0.00740944  |       0  |       0  | 0.012232416  | 0.067551993  |  0.028637196  |  0.141159809  |
     |-----------------------------------------------------------------------------------------------------------------|
     |         r95 |     r94 |     r93 |         r92 |         r91 |         r90 |         r89 |         r88 |     r87 |
     | 0.017432605 |       0 |       0 | 0.038946872 | 0.023241461 | 0.028099947 | 0.014872889 | 0.013193315 |       0 |
     |-----------------------+-----------------------------------------------------------------+-----------------------|
     |     r86 |         r85 |         r84 |         r83 |         r82 |         r81 |     r80 |     r79 |         r78 |
     |       0 | 0.009424859 | 0.047683868 | 0.144871614 | 0.169130026 | 0.052681762 |       0 |       0 |           0 |
     |-----------------------------------------------------------------------------------------------------------------|
     |         r77  |         r76  |         r75  |         r74  |     r73  |     r72  |          r71  |          r70  |
     | 0.334073463  | 0.166731588  | 0.019082396  | 0.007028923  |       0  |       0  |  0.181443957  |  0.096645602  |
     |--------------+--------------+--------------+------------------------------------+---------------+---------------|
     |         r69  |         r68  |         r67  |     r66  |     r65  |         r64  |          r63  |          r62  |
     | 0.000947788  | 0.033319307  | 0.026985634  |       0  |       0  |  0.02489137  |  0.035205119  |  0.010132548  |
     |--------------+--------------+------------------------------------+--------------+---------------+---------------|
     |         r61  |         r60  |     r59  |     r58  |         r57  |         r56  |          r55  |          r54  |
     | 0.011961505  | 0.026992274  |       0  |       0  | 0.013779887  | 0.012240536  |   0.01562337  |  0.007430331  |
     |-----------------------------------------------------------------------------------------------------------------|
     |         r53 |     r52 |     r51 |         r50 |         r49 |         r48 |         r47 |         r46 |     r45 |
     |           0 |       0 |       0 | 0.018373997 | 0.001149529 | 0.019631954 | 0.019027792 | 0.004312979 |       0 |
     |-----------------------+-----------------------------------------------------------------+-----------------------|
     |     r44 |         r43 |         r42 |         r41 |         r40 |         r39 |     r38 |     r37 |         r36 |
     |       0 | 0.006681996 | 0.006499606 | 0.015288497 |           0 | 0.006407483 |       0 |       0 | 0.000397974 |
     |-----------------------------------------------------------------------------------------------------------------|
     |         r35  |         r34  |         r33  |         r32  |     r31  |     r30  |          r29  |          r28  |
     | 0.014097461  |  0.00264816  | 0.016946865  | 0.011763466  |       0  |       0  |  0.021116281  |  0.015574405  |
     |--------------+--------------+--------------+------------------------------------+---------------+---------------|
     |         r27  |         r26  |         r25  |     r24  |     r23  |         r22  |          r21  |          r20  |
     | 0.002311419  | 0.004270459  | 0.004502853  |       0  |       0  | 0.004249185  |  0.009308421  |  0.006450584  |
     |--------------+--------------+------------------------------------+--------------+---------------+---------------|
     |         r19  |         r18  |     r17  |     r16  |         r15  |         r14  |          r13  |          r12  |
     | 0.001228701  | 0.004798659  |       0  |       0  | 0.005186722  | 0.009131205  |  0.003397551  |  0.000437979  |
     |-----------------------------------------------------------------------------------------------------------------|
     |         r11 |     r10 |      r9 |          r8 |          r7 |          r6 |          r5 |          r4 |      r3 |
     |           0 |       0 |       0 | 0.002565321 | 0.003574047 | 0.014967632 | 0.005797702 | 0.010820836 |       0 |
     |-----------------------------------------------------------------------------------------------------------------|
     |                  r2              |                      r1              |                       r0              |
     |                   0              |                       0              |                        0              |
     +-----------------------------------------------------------------------------------------------------------------+

secondly: I have used this command

  destring _all, replace

Thirdly:
I have got the following message

 destring _all, replace
code: contains nonnumeric characters; no replace
com_name: contains nonnumeric characters; no replace
r365: all characters numeric; replaced as byte
r364: all characters numeric; replaced as double
r363: all characters numeric; replaced as double
r362: all characters numeric; replaced as double
r361: all characters numeric; replaced as double
r360: all characters numeric; replaced as byte
r359: all characters numeric; replaced as byte
r358: all characters numeric; replaced as double
r357: all characters numeric; replaced as double


. 

[Clyde Schechter]

Well the variables code and com_name are obviously not numeric and the command tells you it is not replacing them, accordingly. The output also shows the other variables are being replaced as numeric.

So the question is why the output of describe shows them as strings. One possibliity is that the -describe- command was run before -destring- and reflects the state of the data as it started. More likely, I'll guess that before running -destring- you -preserve-d the data set and it restored automatically (or at your command) before the -describe- was run.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[William Lisowski]

If Clyde's advice doesn't resolve your problem, I suggest the following test. Run the 5 lines of code below (type them into the Do-file Editor and run all 5 at the same time) and then copy the commands and output from the Stata Results window and paste it into a reply.

* before
describe code com_name r36*
destring code com_name r36*
* after
describe code com_name r36*

This will test the destring on just 8 variables, a lot less to look at, so you should copy and paste all the commands and output.

[ALKEBSEE RADWAN]

If Clyde's advice doesn't resolve your problem, I suggest the following test. Run the 5 lines of code below (type them into the Do-file Editor and run all 5 at the same time) and then copy the commands and output from the Stata Results window and paste it into a reply.

* before
describe code com_name r36*
destring code com_name r36*
* after
describe code com_name r36*

This will test the destring on just 8 variables, a lot less to look at, so you should copy and paste all the commands and output.

Thank you so much,
I actually tried Clyde's suggestion but the issue is not solving.

I have run the codes you gave and i just replaced variables. please see the outcomes below

describe r34 r35 r36

              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------------------------------
r34             str11   %11s                  R34
r35             str11   %11s                  R35
r36             str11   %11s                  R36

. 
. destring r34 r35 r36, replace
r34: contains nonnumeric characters; no replace
r35: contains nonnumeric characters; no replace
r36: contains nonnumeric characters; no replace

. describe r34 r35 r36

              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------------------------------
r34             str11   %11s                  R34
r35             str11   %11s                  R35
r36             str11   %11s                  R36

.

[William Lisowski]

This output is much different than what you showed in post #6.

Some of your values of these variables - which you did not show us in post #6 - have nonnumeric characters - perhaps commas, perhaps "NA" meant to indicate missing values, perhaps something else entirely.

list r34 if missing(real(r34))

will show you all the observations of r34 that cannot be interpreted as a number. You need to either (a) add appropriate options to the destring command or (b) replace the inappropriate values before again running destring. The output of help destring will provide guidance.

[ALKEBSEE RADWAN]

@William Lisowski thank you so much
I have run this code and i got the following

 list r34 if missing(real(r34))

      +---------+
      |     r34 |
      |---------|
3925. | #VALUE! |
4039. | #VALUE! |
4081. | #VALUE! |
4082. | #VALUE! |
4083. | #VALUE! |
      |---------|
4084. | #VALUE! |
4085. | #VALUE! |
4086. | #VALUE! |
4087. | #VALUE! |
4088. | #VALUE! |
      |---------|
4089. | #VALUE! |
4090. | #VALUE! |
4091. | #VALUE! |
4092. | #VALUE! |
4093. | #VALUE! |
      |---------|
4094. | #VALUE! |
4095. | #VALUE! |
4096. | #VALUE! |
4097. | #VALUE! |
4098. | #VALUE! |
      |---------|
4099. | #VALUE! |
4240. | #VALUE! |
4241. | #VALUE! |
4349. | #VALUE! |
4359. | #VALUE! |
      |---------|
4535. | #VALUE! |
4536. | #VALUE! |
4537. | #VALUE! |
4631. | #VALUE! |
5805. | #VALUE! |
      |---------|
5895. | #VALUE! |
6426. | #VALUE! |
6427. | #VALUE! |
6428. | #VALUE! |
6429. | #VALUE! |
      |---------|
6430. | #VALUE! |
6431. | #VALUE! |
6606. | #VALUE! |
7532. | #VALUE! |
9002. | #VALUE! |
      |---------|
9054. | #VALUE! |
9061. | #VALUE! |
9070. | #VALUE! |
9071. | #VALUE! |
9072. | #VALUE! |
      |---------|
9073. | #VALUE! |
9074. | #VALUE! |
9075. | #VALUE! |
9076. | #VALUE! |
9077. | #VALUE! |
      |---------|
9078. | #VALUE! |
9079. | #VALUE! |
9080. | #VALUE! |
9081. | #VALUE! |
9082. | #VALUE! |
      |---------|
9083. | #VALUE! |
9084. | #VALUE! |
9085. | #VALUE! |
      +---------+

.

kindly how can i remove or fix this issue?

[William Lisowski]

You have two basic choices.

Your data came from Excel. Excel reported problems in some calculated values by presenting the result as "#VALUE!"

The professional approach is to look at the data in Excel, try to understand what caused the error in the formula, and correct the formula so that the result is meaningful. At the same time, they will review all the data in Excel to see if there are other warnings besides "#VALUE!" that need to be corrected, such as perhaps "#DIV/0!.

If they choose to accept the Excel worksheet as it is - perhaps it came from an outside source and they prefer to fix the problems in their Stata program - then they will replace values of "VALUE!" in their string variables with a missing Stata missing value indicator (. or .a or ... or .z) prior to the destring command.

ds, has(type string)
foreach var in `r(varlist)' {
    display "processing `var'"
    replace `var' = "." if `var'=="#VALUE!"
    destring `var', replace
}

If the destring command fails, then the professional knows they missed some other non-numeric value that needs to be understood and taken care of, either in the Excel worksheet or with a second replace command in the loop.

By contrast, the unprofessional approach is to skip the important work of understanding the problems in the Excel worksheet and correcting them in Excel or replacing those values in Stata, and will instead use the force option on the destring command to cause any and all non-numeric values to be replaced by a Stata missing value, and will as a consequence perhaps lose potentially important information.

destring _all, replace force

[ALKEBSEE RADWAN]

You have two basic choices.

Your data came from Excel. Excel reported problems in some calculated values by presenting the result as "#VALUE!"

The professional approach is to look at the data in Excel, try to understand what caused the error in the formula, and correct the formula so that the result is meaningful. At the same time, they will review all the data in Excel to see if there are other warnings besides "#VALUE!" that need to be corrected, such as perhaps "#DIV/0!.

If they choose to accept the Excel worksheet as it is - perhaps it came from an outside source and they prefer to fix the problems in their Stata program - then they will replace values of "VALUE!" in their string variables with a missing Stata missing value indicator (. or .a or ... or .z) prior to the destring command.

ds, has(type string)
foreach var in `r(varlist)' {
display "processing `var'"
replace `var' = "." if `var'=="#VALUE!"
destring `var', replace
}

If the destring command fails, then the professional knows they missed some other non-numeric value that needs to be understood and taken care of, either in the Excel worksheet or with a second replace command in the loop.

By contrast, the unprofessional approach is to skip the important work of understanding the problems in the Excel worksheet and correcting them in Excel or replacing those values in Stata, and will instead use the force option on the destring command to cause any and all non-numeric values to be replaced by a Stata missing value, and will as a consequence perhaps lose potentially important information.

destring _all, replace force

thank you so much