program define tosql
*! cfb v1.1.0  1531 revised for file
* cfb v1.0.4  1326 corr for marksample, add cre option, line length, embedded sq
* cloned from torats.ado 1.2.1

        version 7.0
        syntax [varlist] [if] [in] ,TABLE(string) [Case UPPER CRE]
        local more : set more
        set more off
		di
        di "Output file will be `table'.sql, replace"
        di
		if "`case'" == "" {
			local cw "novar"
			}
		else {
			local cwd "after casewise deletion"
		}
		marksample touse, strok `cw'
		qui summ `touse',meanonly
		local smpl=r(N)
		local nobs=r(sum)
		if `nobs' == 0 {
			error 2000
			exit
		}
		di "Selected dataset contains `nobs' rows `cwd'"
		di
		if "`upper'" == "" {
			local uc = "`table'"
			local outfile = "`table'.sql"
			local crefile = "cre_`table'.sql"
			}
		else {
			local uc = upper("`table'")
			local outfile = "`uc'.SQL"
			local crefile = "CRE_`uc'.SQL"
		}
		if "`cre'" != "" {
			di "Table creation script written to cre_`table'.sql, replace"
			di
			qui file open scr using `crefile',write replace
		}
		if "`cre'" != "" {
				if "`upper'" == "" {
					file write scr "create table `table' (" _n
					}
				else {
					file write scr "CREATE TABLE `UC' (" _n
					}
				}
        local outlist "insert into `uc' ("
        tokenize "`varlist'"
        local i 1
        while "``i''" != "" {
        		local j = `i'+1
        		local s`i' = 0
                local type : type ``i''
*             dis "`i'  `type'"
                if substr("`type'",1,3) == "str" { local s`i' = 1}
                		if "`upper'" == "" {
	                        local outlist "`outlist'``i''"
	                        }
	                    else {
	                    	local k = upper("``i''")
	                    	local outlist "`outlist'`k'"
	                    }
                        if "``j''" != "" {
                        	local outlist "`outlist',"
                        	if mod(`i',10) == 0 {local outlist "`outlist'" _n " "}
                        	}
                        else {
                        	local outlist "`outlist')"
                        	}
                if "`cre'" != "" {
					local tt = "`type'"
					if "`tt'" == "byte" { local tt = "int"}
                	if `s`i'' == 1 {
                		local tt = "char("+substr("`type'",4,2)+")"
                		}
                    if "``j''" != "" {
                        	local tt "`tt',"
                        	}
                    else {
                        	local tt "`tt');"
                        	}
					if "`upper'" == "" {
						file write scr "``i'' `tt'" _n
						}
					else {
						local up = upper("``i''")
						local ut = upper("`tt'")
					    file write scr "`up' `ut'" _n
					    }
				}
                local i = `i' + 1
        }
        if "`cre'" != "" {
        	file write scr _n
        	file close scr
        	}
 		qui file open sql using `outfile',write replace
        local n 1
        while `n'<=`smpl' {
        	if `touse'[`n']>0 {
        		local j 1
        		local row " values ("
        		while "``j''" != "" {
        			local k = `j'+1
        			if `s`j'' == 0 {
        				if ``j''[`n'] !=. {
	        				local val = ``j''[`n']
	        				}
	        			else {
	        				local val = "NULL"
	        				}
        				}
        			else {
* deal with embedded single quote
        				local sv = subinstr(``j''[`n'],"'","_",.)
        				local val = "'`sv''"
*        				local val = "'"+``j''[`n']+"'"
        			}
        			if "``k''" != "" {
                        	local row "`row'`val',"
                        	if mod(`j',10) == 0 {local row "`row'" _n " "}
                        	}
                        else {
                        	local row "`row'`val');"
                        	}
        			local j = `j'+1
        			}
        		file write sql "`outlist'"
        		file write sql "`row'" _n
	       		}
         		local n = `n'+1
        	}
        file close sql
        set more `more'
end
exit