Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- capture program drop odbc2create
- *! version 1.1
- * Update 5/23: Now checks if there are any date columns. Simple commands if not.
- * Modified ODBC command that takes all Stata dates and inserts as DATE
- * Written for MySQL. Drops the table in question if it exists already.
- * Only use with create. If you are appending an existing table, then you need to do the date conversion in Stata.
- * By Michael Ewens with invaluable guidance from Joseph Coveney
- * Built from this thread: http://www.stata.com/statalist/archive/2010-05/msg01190.html
- * 5-20-2010
- * syntax odbc2create, [t]able(string) dsn() u()
- version 7.0
- program define odbc2create
- syntax [varlist], Table(string) [*]
- if ("`varlist'" == "") {
- local varlist _all
- }
- else {
- * no op
- }
- * Get a list of the date variables
- local date_vars
- local count = 0
- foreach variable of varlist `varlist' {
- local variable_format : format `variable'
- if (strpos("`variable_format'", "%t") == 1) {
- * May not have any dates
- local count = `count' + 1
- local date_vars `date_vars' `variable'
- }
- else {
- }
- }
- * Drop if table exists
- local string "DROP TABLE IF EXISTS "
- local drop_s `string'`table'
- if(`count' == 0) {
- odbc exec("`drop_s'"), `options'
- odbc insert `varlist', table("`table'") create `options'
- }
- else {
- * Format the date for MySQL
- format `date_vars' %tdCCYY-NN-DD
- preserve
- qui tostring `date_vars', force replace usedisplayformat
- odbc exec("`drop_s'"), `options'
- * Create table
- odbc insert `varlist', table("`table'") create `options'
- * Alter columns for date
- foreach variable of varlist `date_vars' {
- odbc exec("ALTER TABLE `table' MODIFY `variable' DATE"), `options'
- }
- restore
- }
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement