CODEWITHSUNDEEP
excelvariablesimportstata
Caspar
Caspar

Reputation: 19

read Excel cells into Stata global as variables

There are several panel datasets I'd like to join.

The observations in these datasets are identified by an id variable and a variable identifying the time the observation was made. All datasets include some variables I need, some I don't need and never the same variables (excluding the id and the survey-time-variable).

The datasets are huge, so to speed up processing and decrease the size of the final dataset, I want to drop all variables I don't need.

Thoose variables are stored in an Excel file with several columns. One of these columns, say, C2, contains all my variable names.

Now I believe I have two possible ways to follow, which I describe in order of my favour:

1) Read in the variable-names from the Excel file into a global in Stata and then state: 

keep global varlist

That way only variables I need should be retained.

2) Generate a new empty dataset with all the variables from the Excel file and then 

joinby id syear $varlist using dataset, update

Anyhow I need to read out the variables from the excel file into a global. I realize that I could do this by just copy and paste from Excel into Stata, but I suppose there will be some changes in the variables as I go along with the work and I want to learn how to program properly in Stata.

Upvotes: 0

Views: 1028

Answers (1)

Roberto Ferrer
Roberto Ferrer

Reputation: 11102

I would recommend reading the variable names into a local, and use only global if strictly necessary.

One way to do that is to use import excel along with levelsof:

clear
set more off

// import from MS Excel and create local
import excel using myvars.xlsx, cellrange(B2:B5) firstrow
levelsof myvars, local(tokeep) clean

// use local
clear
sysuse auto
describe

keep `tokeep'
describe

The MS Excel file reads

myvars
make
mpg
weight

in the corresponding cells.

The result : 

. // import from MS Excel and create local
. import excel using myvars.xlsx, cellrange(B2:B5) firstrow

. levelsof myvars, local(tokeep) clean
make mpg weight

. 
. // use local
. clear

. sysuse auto
(1978 Automobile Data)

. describe

Contains data from C:\Program Files (x86)\Stata13\ado\base/a/auto.dta
  obs:            74                          1978 Automobile Data
 vars:            12                          13 Apr 2013 17:45
 size:         3,182                          (_dta has notes)
--------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------------------------------------
make            str18   %-18s                 Make and Model
price           int     %8.0gc                Price
mpg             int     %8.0g                 Mileage (mpg)
rep78           int     %8.0g                 Repair Record 1978
headroom        float   %6.1f                 Headroom (in.)
trunk           int     %8.0g                 Trunk space (cu. ft.)
weight          int     %8.0gc                Weight (lbs.)
length          int     %8.0g                 Length (in.)
turn            int     %8.0g                 Turn Circle (ft.)
displacement    int     %8.0g                 Displacement (cu. in.)
gear_ratio      float   %6.2f                 Gear Ratio
foreign         byte    %8.0g      origin     Car type
--------------------------------------------------------------------------------------------------------------------
Sorted by:  foreign

. 
. keep `tokeep'

. describe

Contains data from C:\Program Files (x86)\Stata13\ado\base/a/auto.dta
  obs:            74                          1978 Automobile Data
 vars:             3                          13 Apr 2013 17:45
 size:         1,628                          (_dta has notes)
--------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------------------------------------
make            str18   %-18s                 Make and Model
mpg             int     %8.0g                 Mileage (mpg)
weight          int     %8.0gc                Weight (lbs.)
--------------------------------------------------------------------------------------------------------------------
Sorted by:  
     Note:  dataset has changed since last saved

. 
end of do-file

See also help file read, but for your case, it seems an unnecessarily complicated path.

Upvotes: 1

Related Questions