Problem with database when i Import

[Tahm Tahm]

Hello everyone,

I am not an english speaker but i will do my best !

So i am a beginner with Stat i have the 14.1 version.

I have a database in .xlsx and i imported it with stat but when i want to regress it doesn't work and variable is not found : r(111).

I wonder if i can send the database here and if you can help me to understand what the problem is.

Hope you can help me, have a nice day.

[Marcos Almeida]

Hello Tahm,

Welcome to the Statalist/Stata Forum.

To start, a few notes:

Instead of "Stat" or "stat", the correct spelling is Stata.

You said you have the 14.1 version. I kindly suggest you to (freely) update it to the current version, which is 14.2.

As recommend in the FAQ, please prefer to provide the commands and output under CODE delimiters.

These general aspects being commented, and now going directly to your point, I gather you got string variables when converting from .xlsx to .dta.

Please present the results of

. describe

Hopefully that helps.

[daniel klein]

Please post the code that you have used and show what Stata responded. Use [CODE] delimiters to do this (as I do below). Also show the results of

describe

after you imported the data.

Best
Daniel

[Tahm Tahm]

Yes my mistake it's Stata

Code:

. describe

Contains data
obs: 612
vars: 109
size: 1,296,828
-------------------------------------------------------------------------------------------------------------------------------------------------------------
storage display value
variable name type format label variable label
-------------------------------------------------------------------------------------------------------------------------------------------------------------
A str9 %9s
B str20 %20s
C str14 %14s
D str18 %18s
E str30 %30s
F str4 %9s
G str4 %9s
H str16 %16s
I str16 %16s
J str47 %47s
K str17 %17s
L str17 %17s
M str11 %11s
N str26 %26s
O str26 %26s
P str13 %13s
Q str14 %14s
R str18 %18s
S str17 %17s
T str9 %9s
U str13 %13s
V str19 %19s
W str14 %14s
X str26 %26s
Y str20 %20s
Z str15 %15s
AA str13 %13s
AB str20 %20s
AC str11 %11s
AD str15 %15s
AE str13 %13s
AF str26 %26s
AG str22 %22s
AH str36 %36s
AI str24 %24s
AJ str24 %24s
AK str32 %32s
AL str30 %30s
AM str30 %30s
AN str11 %11s
AO str16 %16s
AP str33 %33s
AQ str17 %17s
AR str25 %25s
AS str11 %11s
AT str17 %17s
AU str10 %10s
AV str34 %34s
AW str55 %55s
AX str51 %51s
AY str18 %18s
AZ str8 %9s
BA str16 %16s
BB str10 %10s
BC str19 %19s
BD str22 %22s
BE str15 %15s
BF str17 %17s
BG str27 %27s
BH str22 %22s
BI str26 %26s
BJ str9 %9s
BK str9 %9s
BL str20 %20s
BM str19 %19s
BN str27 %27s
BO str15 %15s
BP str15 %15s
BQ str14 %14s
BR str1 %9s
BS str1 %9s
BT str15 %15s
BU str18 %18s
BV str4 %9s
BW str4 %9s
BX str21 %21s
BY str25 %25s
BZ str24 %24s
CA str28 %28s
CB str15 %15s
CC str23 %23s
CD str21 %21s
CE str24 %24s
CF str17 %17s
CG str23 %23s
CH str1 %9s
CI str38 %38s
CJ str45 %45s
CK str1 %9s
CL str20 %20s
CM str14 %14s
CN str18 %18s
CO str25 %25s
CP str23 %23s
CQ str21 %21s
CR str36 %36s
CS str19 %19s
CT str23 %23s
CU str23 %23s
CV str19 %19s
CW str40 %40s
CX str19 %19s
CY str24 %24s
CZ str42 %42s
DA str33 %33s
DB str4 %9s
DC byte %10.0g
DD byte %10.0g
DE double %10.0g
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorted by:
Note: Dataset has changed since last saved.

[Marcos Almeida]

Every variable with type "str" is a string variable. You will need to examine what happened. You may wish to check the commands encode and destring as well.

[Patricio Mosquera]

When it occurs, I usually use this

destring, replace 

[Tahm Tahm]

I tried command destring, replace

. destring, replace
A contains nonnumeric characters; no replace
B contains nonnumeric characters; no replace
C contains nonnumeric characters; no replace
D contains nonnumeric characters; no replace
E contains nonnumeric characters; no replace
F contains nonnumeric characters; no replace
G contains nonnumeric characters; no replace
H contains nonnumeric characters; no replace
I contains nonnumeric characters; no replace
J contains nonnumeric characters; no replace
K contains nonnumeric characters; no replace
L contains nonnumeric characters; no replace
M contains nonnumeric characters; no replace
N contains nonnumeric characters; no replace
O contains nonnumeric characters; no replace
P contains nonnumeric characters; no replace
Q contains nonnumeric characters; no replace
R contains nonnumeric characters; no replace
S contains nonnumeric characters; no replace
T contains nonnumeric characters; no replace
U contains nonnumeric characters; no replace
V contains nonnumeric characters; no replace
W contains nonnumeric characters; no replace
X contains nonnumeric characters; no replace
Y contains nonnumeric characters; no replace
Z contains nonnumeric characters; no replace
AA contains nonnumeric characters; no replace
AB contains nonnumeric characters; no replace
AC contains nonnumeric characters; no replace
AD contains nonnumeric characters; no replace
AE contains nonnumeric characters; no replace
AF contains nonnumeric characters; no replace
AG contains nonnumeric characters; no replace
AH contains nonnumeric characters; no replace
AI contains nonnumeric characters; no replace
AJ contains nonnumeric characters; no replace
AK contains nonnumeric characters; no replace
AL contains nonnumeric characters; no replace
AM contains nonnumeric characters; no replace
AN contains nonnumeric characters; no replace
AO contains nonnumeric characters; no replace
AP contains nonnumeric characters; no replace
AQ contains nonnumeric characters; no replace
AR contains nonnumeric characters; no replace
AS contains nonnumeric characters; no replace
AT contains nonnumeric characters; no replace
AU contains nonnumeric characters; no replace
AV contains nonnumeric characters; no replace
AW contains nonnumeric characters; no replace
AX contains nonnumeric characters; no replace
AY contains nonnumeric characters; no replace
AZ contains nonnumeric characters; no replace
BA contains nonnumeric characters; no replace
BB contains nonnumeric characters; no replace
BC contains nonnumeric characters; no replace
BD contains nonnumeric characters; no replace
BE contains nonnumeric characters; no replace
BF contains nonnumeric characters; no replace
BG contains nonnumeric characters; no replace
BH contains nonnumeric characters; no replace
BI contains nonnumeric characters; no replace
BJ contains nonnumeric characters; no replace
BK contains nonnumeric characters; no replace
BL contains nonnumeric characters; no replace
BM contains nonnumeric characters; no replace
BN contains nonnumeric characters; no replace
BO contains nonnumeric characters; no replace
BP contains nonnumeric characters; no replace
BQ contains nonnumeric characters; no replace
BR contains nonnumeric characters; no replace
BS contains nonnumeric characters; no replace
BT contains nonnumeric characters; no replace
BU contains nonnumeric characters; no replace
BV contains nonnumeric characters; no replace
BW contains nonnumeric characters; no replace
BX contains nonnumeric characters; no replace
BY contains nonnumeric characters; no replace
BZ contains nonnumeric characters; no replace
CA contains nonnumeric characters; no replace
CB contains nonnumeric characters; no replace
CC contains nonnumeric characters; no replace
CD contains nonnumeric characters; no replace
CE contains nonnumeric characters; no replace
CF contains nonnumeric characters; no replace
CG contains nonnumeric characters; no replace
CH contains nonnumeric characters; no replace
CI contains nonnumeric characters; no replace
CJ contains nonnumeric characters; no replace
CK contains nonnumeric characters; no replace
CL contains nonnumeric characters; no replace
CM contains nonnumeric characters; no replace
CN contains nonnumeric characters; no replace
CO contains nonnumeric characters; no replace
CP contains nonnumeric characters; no replace
CQ contains nonnumeric characters; no replace
CR contains nonnumeric characters; no replace
CS contains nonnumeric characters; no replace
CT contains nonnumeric characters; no replace
CU contains nonnumeric characters; no replace
CV contains nonnumeric characters; no replace
CW contains nonnumeric characters; no replace
CX contains nonnumeric characters; no replace
CY contains nonnumeric characters; no replace
CZ contains nonnumeric characters; no replace
DA contains nonnumeric characters; no replace
DB contains nonnumeric characters; no replace
DC already numeric; no replace
DD already numeric; no replace
DE already numeric; no replace

Is it possible that the problem is from the Database ?

[Rich Goldstein]

you might want to investigate the issue some more; here is an example:

ta DB if real(DB)==.

this will tabulate only those observations of DB that have nonnumeric characters

[Nick Cox]

The problem here may be metadata in the dataset, especially, but not only, in the first few observations. Most spreadsheet data files are unsuitable for direct import into Stata. That's why (e.g.) import excel has options to start with rows other than the first.

[William Lisowski]

Since the variables were given the names of the spreadsheet columns (A, B, ..., DE) my guess, building on Nick's response, is that Excel has column titles in the first row, but neither of the firstrow options were used with import excel. But the problem could be even deeper, as Nick suggests.

It will help you to review the output of Stata's help import excel command and add the appropriate options to your import excel command, or work with the options interactively using the Import selection on Stata's File menu.

[Marcos Almeida]

As Stata correctly points out in #7, we cannot "destring" when there are nonnumeric characters. That why I suggested in #5 to check whether you'd need to encode or destring.

But it seems Nick and William unveiled the issue. Being this so, Tahm could just repeat the import with the "firstrow" option and see what happens.