View Full Version : Import of Excel file into Stata!

Maastricht2006

04-02-2011, 08:25 AM

Hello everyone,

I am a beginner with stata. I tried to import an excel file with ODBC data source.

I already shortened the variable names without empty spaces and less than 32 characters. I only used 4 variables. Data Type is str244.

I insert the data and get no error message. However, if I go on summarize to show number of observations, mean etc. Stata does not show any results

Does somebody know this problem and can tell me what I am doing wrong?

That would be very nice!

zerostin

04-02-2011, 10:19 AM

Can you maybe upload some sample data?

Maastricht2006

04-02-2011, 12:55 PM

yes no problem, here is the excel file

zerostin

04-02-2011, 01:23 PM

If I try to use your data I don't get any observations. If I recreate a part of your data, then I don't have any problems.

+------------------------------------------------------+

| cusip year compan~e state Gindex classi~d |

|------------------------------------------------------|

1. | 00751Y 2002 test_1 de 8 0 |

2. | 00751Y 2004 test_2 de 9 0 |

3. | 00751Y 2006 test_3 de 8 0 |

+------------------------------------------------------+

So, I guess the problem is the excel file, but I don't know what is causing that problem. Perhaps somebody has a better solution, otherwise you maybe need to recreate your excel file.

bukharin

04-03-2011, 07:18 AM

It's a pretty strange Excel file; eg what happened to rows 21-34? I can't seem to unhide them (then again I'm pretty useless in Excel...)

I copied and pasted the data from Excel to Stata which seemed to work okay, but only copied over the observations that weren't hidden. I suspect if you "unhid" the other observations you could simply copy and paste.

Otherwise I would recommend finding someone with a copy of Stat/Transfer which makes this type of conversion very easy.

Maastricht2006

04-03-2011, 07:44 AM

thanks...probably the fact that I used a filter in the excel file why some values were hidden was the problem...So I will just copy/paste the data in new excel file...or I use my demoverison of Stat/Transfer

bukharin

04-03-2011, 07:53 AM

Ah of course, the filters. I tried it with the filters disabled and copying/pasting worked fine.

Don't use a demo version of Stat/Transfer for anything important - it will randomly delete approx 1 in 16 observations. The idea apparently is that you can see what the program can do, but have to buy it to actually do anything useful.

Maastricht2006

04-03-2011, 12:27 PM

thx for the advice...

However, I am already dealing with the next problem.

In the Excel file, I refer to the first sheet and the 6digit Cusips. The cusips are company identifiers and represent firms that introduced antitakeover provisions, such as classified boards, over the period from 1997 to 2009. Classified Boards is an independent governance variable that I use in my analysis. Most of the cusips appear several times for one firm, because they refer to the different years from 1997 to 2009.

In the second, I have other control variables. They measure size, leverage, risk. In the third sheet I have the dependent variable profitabiity measured by e.g. Operating profit margin. The 6digit cusips (Cusip6) are mentioned as well. As not all firms introduced antitakeover provisions, the first sheet does not have all cusips of Sheet 2 and 3.

I want to merge the data from sheet 2 and 3 according to the Cusisp of the first sheet. Then I want to import the data into Stata to do the regression.

Does somebody now a function how to do that in Excel?

Maastricht2006

04-05-2011, 08:42 AM

hey everyone, its ok, I have already found a solution to this problem

Maastricht2006

04-05-2011, 10:14 AM

hello...

I am trying to convert the two attachted Excel files into Stata.

with the first file I get this error message

1. odbc load, dsn("Excel Files") table("Sheet1$") dialog(complete)

Annualized_S&P500_return_ invalid name

- converted Annualized_S&P500_return_ to var2

with the second file I get this error message

2. odbc load, dsn("Excel Files") table("'G index 97-06 $'") dialog(complete)

you must start with an empty dataset

r(18);

I dont really understand this. Does somebody know the solution to the problem?

or even better: Does someone know a site where to download the full version of stat transfer for free?^^

regards

Maastricht2006

04-05-2011, 10:34 AM

here the two attachments that i mention above

zerostin

04-05-2011, 11:52 AM

hello...

I am trying to convert the two attachted Excel files into Stata.

with the first file I get this error message

1. odbc load, dsn("Excel Files") table("Sheet1$") dialog(complete)

Annualized_S&P500_return_ invalid name

- converted Annualized_S&P500_return_ to var2

Try to delete the " &" in your variable names and try it again?

with the second file I get this error message

2. odbc load, dsn("Excel Files") table("'G index 97-06 $'") dialog(complete)

you must start with an empty dataset

r(18);

I dont really understand this. Does somebody know the solution to the problem?

or even better: Does someone know a site where to download the full version of stat transfer for free?^^

regards

If you click submit, he imports the data. If you afterwards click on okay, you get this error, but the data is already imported.

If that's not the case:

Import sheet 1, save sheet1.dta;

clear

Import sheet 2, save sheet2.dta

clear

You can't import sheet 1, 2 and 3 in one dataset as far as i know. You need to merge them afterwards.

Maastricht2006

04-13-2011, 07:17 AM

Hey everyone,

after importing my file into stata, stata does not read some variables. I assume that they are not coded correctly and wanted to change them from string to numeric by using destring, real() or encode. However, it does not work I always get error messages like these:

CUSIP6encode

unrecognized command: CUSIP6encode

r(199);

. real( CUSIP6)

unrecognized command: real

r(199);

. destring CUSIP6

must specify either generate or replace option

r(198);

I already put a lable next to the variable name, but this is also not working.

Anybody knows what I am doing wrong?

duskstar

04-13-2011, 08:15 AM

As the error message says you need to specify a generate or replace. For example, try:

replace CUSIP6=real(CUSIP6)

or

gen CUSIP6_new = real(CUSIP6)

Maastricht2006

04-25-2011, 08:53 AM

Thank you.

In the meantime I am already dealing with the next problem.

If I type summarize I do not get any values for State_of_Inc or State_of_Inclower. I added State_of_Inclower because I thought the capital letters of State_of_Inc could be the problem, but it still does not find values.

The same is valid for Company and Companylower. I use string as datatype because the variables represent text.

Does somebody know the solution to the problem?1305

zerostin

04-26-2011, 09:04 AM

That's because there aren't any numbers in these variables. So, it doesn't make any sense, and it is not possible, to calculate mean, SD, etc.

Powered by vBulletin™ Version 4.1.3 Copyright © 2014 vBulletin Solutions, Inc. All rights reserved.