Stata programming and putexcel-loop

Question

I'm trying to make an automated Excel file that documents the number of observations dropped during my sample construction, using putexcel and a simple program.

I'm pretty new to programming, but the program below does the job. It stores 4 global macros for each time I drop some observations: 1) Number of observations dropped, 2) Share of observations dropped, 3) Number of observations left in the data set and 4) a string that describes why I drop the observations.

To export the results to excel I use the putexcel-command -- which is working fine. The problem is that I need to drop observations a lot of times in the dofile and I wondered if I could somehow incorporate the putexcel part in the program to make it loop over cells.

In other words, what I want is the program to automatically save the description ($why) in A1 the first time, in A8 the second time and so on.

I have provided an example of my code below:

 ** Generate some data: 
clear

input id year wage 
1   1   200 
1   2   250
1   3   300
2   1   152
2   2   150
2   3   140
3   1   300
3   2   320
3   3   360
end

** Define program 
cap program drop dropdata
program define dropdata
    count
    global N = r(N)
    count if `1' 
    global drop = r(N) 
    global share = ($drop/$N)
    drop if `1' 
    count
    global left = r(N) 
    global why = "`2'" 
end 

** Drop if first year 
dropdata year==1 "Drop if first year" 

** Export to excel 
putexcel set "documentation.xlsx", modify 
putexcel A1 = ("$why")
putexcel A3 = ("Obs. dropped") A4 = ("Share dropped") A5 = ("Observations left")
putexcel B3 = ($drop) B4 = ($share) B5=($left) 

** Now drop if wage is < 300 
dropdata wage<300 "Drop if wage<300" 

putexcel A8 = ("$why")
putexcel A10 = ("Obs. dropped") A11 = ("Share dropped") A12 = ("Observations left")
putexcel B10 = ($drop) B11 = ($share) B12 = ($left)

Eric HB Eric HB · Accepted Answer · 2017-08-16T02:26:03

The issue with this is that Stata does not know what cells are filled and which are not, so I think it would probably be easiest to include another argument in your program define that says the number of times you have run the program.

Here is an example:

 ** Generate some data: 
clear

input id year wage 
1   1   200 
1   2   250
1   3   300
2   1   152
2   2   150
2   3   140
3   1   300
3   2   320
3   3   360
end

** Define program 
cap program drop dropdata
program define dropdata
    count
    local N = r(N)
    count if `1' 
    local drop = r(N) 
    local share = ($drop/$N)
    drop if `1' 
    count
    local left = r(N) 
    local why = "`2'" 

    local row1 = `3'*7 + 1
    local row3 = `row1' + 2
    local row4 = `row1' + 3
    local row5 = `row1' + 4
    putexcel set "documentation.xlsx", modify 
    putexcel A`row1' = ("`why'")
    putexcel A`row3' = ("Obs. dropped") A`row4' = ("Share dropped") A`row5' = ("Observations left")
    putexcel B`row3' = (`drop') B`row4' = (`share') B`row5' = (`left') 


end 

** Drop if first year 
dropdata year==1 "Drop if first year" 0

** Now drop if wage is < 300 
dropdata wage<300 "Drop if wage<300" 1

Note that the change is to include the number of calls already done as the third argument in dropdata, then we add the putexcel commands to rows based on that number.

As an aside:

I changed all of your globals to locals because they're safer. Also, in general, if you want to return macros from a program that you write, you tell Stata the program is, for example an rclass and then use statements like below:

program define return_2, rclass
    return local asdf 2
end

and then you can access the local asdf (which is equal to 2) as the local r(asdf) and you can check the values of all locals returned by the program with the command return list

Stata programming and putexcel-loop

1 Answers