Import Excel

[Sebastian Grein]

I am importing an excel file Stata with the following command:

import excel using "peer_energy_health_care.xlsx", firstrow clear

When I executed it the first time, I had no issues. The Excel file contains numbers in the following format: 1.000,00, but Stata imports them as 1,000.00.

How can I ensure that it is imported in the format which is used in the excel file?

[Clyde Schechter]

I can only partially help you here. Globally, there are (at least) two ways to punctuate multi-digit numbers. In the US, we use . as the decimal point and , to separate groups of 3 digits to enhance readability. In some other places, notably much of Europe, it is the reverse: . is used to separate groups of 3 digits and , is used as the decimal point.

. set obs 1
Number of observations (_N) was 0, now 1.

. gen x = 1000.00

. format x %10.2fc

. list

     +----------+
     |        x |
     |----------|
  1. | 1,000.00 |
     +----------+

. set dp  comma

. list

     +----------+
     |        x |
     |----------|
  1. | 1.000,00 |
     +----------+

Stata allows you to choose between these two ways of displaying numbers. The -set dp- command, as shown above, gives you the choice.

And I believe that the Excel file you have is using the equivalent of -set dp comma-, because in the US convention (-set dp period-) 1.000,00 is a malformed sequence that does not correspond to any correct number. Somehow in your Stata, it got reset to -set dp period-. If you run -set dp comma- in Stata, then the numbers will look like the ones in Excel.

But there is a caution first. We have to be sure that the Excel number really is supposed to be 1000 and not a somehow mangled representation of 1. So here's a simple test of that. After you have imported the data, take one of these variables and create a new variable that is equal to the square root of the original one. If the underlying number is 1, the variables will agree. If it is 1000, they will be different.