As you no doubt read diligently in my about me page, I currently write mostly in Stata. I've dabbled in Python, and write LaTeX, HTML, a little cURL, and some other things when I need to shell out of Stata. Here you can see some .ados I've written. None of them are available on SSC, and most of them do not have help files (part of the reason they aren't). I presented qualtrics.ado virtually at the Stata Conference 2020. I'll also say, some may have bugs that I haven't gotten around to fixing. There is a short description with each and comments in the syntax command notes what each option does. I'll also note that despite some effort, the indentation did not display properly in all cases in the code blocks, there should be no such issue if you download the files.
qualtrics
-qualtrics- allows you to automate the downloading and processing of survey data from Qualtrics. You can set a password(and optionally user name) so you don't have to dig up your token each time, list, and filter the surveys to get the survey ID and then download and clean it using the JSON storing the survey questions, value labels, etc.
*! version 2.0 3/17/2020: Danial Hoepfner danial.hoepfner@gmail.com
cap program drop qualtrics
program define qualtrics, rclass
version 13
syntax anything, [ /// Set options and those that are used in more than one function
Token(string) /// API Token from Qualtrics, can specify both with each run or set them
Center(string) /// Data Center from Qualtrics can specify both with each run or set them
Password(string) /// Password to set qualtrics or decode
User(string) /// User name option to associate with password if
/// Get options
ID(string) /// Survey ID
CSV(string) /// Where to save the csv
DTA(string) /// Where to save the DTA
VAluelabs /// Save value labels instead of numeric
CLean ///Extract and apply labels and value labels etc.
RELAB ///list out label variable commands to make manual adjustments
REVALlab ///list out value label define commands to make adjustments
PREserve /// Preseve and reload existing dataset (Only relevant for get function)
/// List options
Match(string) /// Survey name to match expression (regular expression)
Active(string) /// Survey is active(true) or inactive(false)
MODRange(string asis) /// Date range survey was last modified
CREATERange(string asis) /// Date range survey was created
/// Undocumented
Method(string) ///Whether to use wget64 or curl, default is curl
]
************************Capturing things I might Change*************************
************************Capturing things I might Change*************************
************************Capturing things I might Change*************************
local original_lines `"`c(linesize)'"'
local restoredata = 0
if `c(k)' != 0 & `=_N' != 0 {
tempfile orginal_data
qui sa `orginal_data'
local restoredata = 1
}
**********************End Capturing things I might Change***********************
**********************End Capturing things I might Change***********************
**********************End Capturing things I might Change***********************
capture which sxpose.ado
if _rc==111 ssc install sxpose.ado
timer clear 13
timer on 13
if `"`method'"'!=`"curl"' local method `"curl"' //Setting curl as default
se linesize 255
if !inlist(`"`anything'"',`"list"',`"get"',`"set"') {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"Please enter -list-, -get- or -set-"'
error 198
}
if `"`clean'"'!=""&`"`valuelabs'"'!="" {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"CLean and VAluelabs options not compatible"'
set linesize `original_lines'
error 184
}
****************************************************Set Data Center and Token*****************************************************
****************************************************Set Data Center and Token*****************************************************
****************************************************Set Data Center and Token*****************************************************
if `"`anything'"' == `"set"' { //If using set function
if inlist(`""',`"`password'"',"`token'","`center'") {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"You must specify Password, Token, and Center"'
error 198
}
local salt =floor(runiform()*50)
while length(`"`password'"') local password `"`password'`password'"'
}
*!DH Note 3/18/2020: Encoding Password
local plen=length(`"`password'"')
local encpassword
forvalues p=1/`plen' {
local curlet=substr(`"`password'"',`p',1)
local char
forvalues i=1/255 {
if `i'!=96 {
if `"`=char(`i')'"'==`"`curlet'"' {
local encpassword `"`encpassword'`:di %03.0f `i''"'
continue, break
}
}
}
}
*!DH Note 3/18/2020: Now adding password character value to token item with a random number
local tlen=length(`"`token'"')
local enctoken
forvalues p=1/`tlen' {
local curlet=substr(`"`token'"',`p',1)
if `p' == 1 local pnum=substr(`"`encpassword'"',`p',3)
if `p' > 1 local pnum=substr(`"`encpassword'"',`p'*3-2,3)
local char
forvalues i=1/255 {
if `i'!=96 {
if `"`=char(`i')'"'==`"`curlet'"' {
local encval= `i'+`pnum'-`salt'
local enctoken `"`enctoken'`:di %03.0f `encval''"'
continue, break
}
}
}
}
*!DH Note 3/18/2020: Saving in personal .ado path
local enctokenstr `"`enctoken'`:di %03.0f `salt''"'
if `"`user'"' != "" {
local ulen=length(`"`user'"')
local warn `"Warning! it is best to only use a-z, A-Z, 0-9, _ and space for your User name"'
local ok =0
forvalues p=1/`ulen' {
local curlet=substr(`"`user'"',`p',1)
foreach l in `c(alpha)' `c(ALPHA)' 0 1 2 3 4 5 6 7 9 9 {
if inlist(`"`curlet'"',`"`l'"',`" "',`"_"') local ++ok
}
}
if `ok' == `ulen' local warn
}
local filecont
cap file close outgo
cap macro drop outgo
file open outgo using `"`c(sysdir_personal)'`user'qualtricscreds.txt"', replace write
local filecont `"`center' `enctokenstr'"'
file write outgo `"`filecont'"'
file close outgo
macro drop outgo
di as smcl `"{text}{hline 160}"'
if `"`warn'"' != `""' di as error `"`warn'"'
di as result `"Data center information and encrypted token saved to `c(sysdir_personal)'`user'qualtricscreds.txt"'
di as result `"You can now run qualtrics using your password only rather than specifying the token and data center"'
di as smcl `"{text}{hline 160}"'
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
exit
}
**************************************************End Set Data Center and Token***************************************************
**************************************************End Set Data Center and Token***************************************************
**************************************************End Set Data Center and Token***************************************************
**********************************************Retrieving Token and Data Center**********************************************
**********************************************Retrieving Token and Data Center**********************************************
**********************************************Retrieving Token and Data Center**********************************************
if `"`anything'"' != `"set"' {
local getqtoken = 1
if `"`token'"' != `""' & `"`center'"' !=`""' local getqtoken = 0
if `"`token'"' == `""' & `"`center'"' ==`""' & `"`password'"' == `""' {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"You Must either specify the Token and Center options or use -qualtrics set- and set a password to encode the qualtrics data center and token"'
error 198
}
if `getqtoken' == 1 {
cap confirm file `"`c(sysdir_personal)'`user'qualtricscreds.txt"'
if _rc != 0 {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"Text file containing data center and encrypted token not found. Check for `c(sysdir_personal)'`user'qualtricscreds.txt or Use -qualtrics set- and set a password to encode the qualtrics data center and token"'
error 198
}
*!DH Note 3/18/2020: Import file with token
clear
mata: input = cat(`"`c(sysdir_personal)'`user'qualtricscreds.txt"')
qui getmata input //Make as variable
tokenize `"`=input[1]'"'
local center `"`1'"'
local enctokenstr `"`2'"'
*!DH Note 3/18/2020: Encoding Password
local plen=length(`"`password'"')
local decpassword
forvalues p=1/`plen' {
local curlet=substr(`"`password'"',`p',1)
local char
forvalues i=1/255 {
if `i'!=96 {
if `"`=char(`i')'"'==`"`curlet'"' {
local decpassword `"`decpassword'`:di %03.0f `i''"'
continue, break
}
}
}
}
local enctoken =substr(`"`enctokenstr'"',1,length(`"`enctokenstr'"')-3)
local salt =substr(`"`enctokenstr'"',-3,3)
while length(`"`decpassword'"') local decpassword `"`decpassword'`decpassword'"'
}
local tlen=length(`"`enctoken'"')
local dectoken
forvalues p = 1(3)`tlen' {
local pnum=substr(`"`decpassword'"',`p',3)
local tnum=substr(`"`enctoken'"',`p',3)
local num = `tnum'-`pnum'+`salt'
local dectoken `"`dectoken'`=char(`num')'"'
}
local token `"`dectoken'"'
}
}
********************************************End Retrieving Token and Data Center********************************************
********************************************End Retrieving Token and Data Center********************************************
********************************************End Retrieving Token and Data Center********************************************
local apiheader `"X-API-TOKEN: `token'"' //Setting header information for api token
local headercont `"Content-Type: application/json"' //Setting another header condition needed
if `"`anything'"' == `"list"' { //If using survey list function
local inpath `"list.txt"' //Setting path to import API response
cap rm `"`inpath'"'
local url `"https://`center'.qualtrics.com/API/v3/surveys"' //Url for this API call
if `"`method'"' == `"curl"' { // If curl
*Call to receive json file with surveys
!curl -H "`apiheader'" "`url'" -o "`inpath'"
}
if `"`method'"' == `"wget64"' { //if wget64
*Call to receive json file with surveys
!wget64 --header="`apiheader'" "`url'" -O "`inpath'"
}
clear
cap confirm file `"`inpath'"'
if _rc !=0 {
di as error `"Unable to find downloaded survey list file to process. Check your token and data center or your password"'
di as error `"On occasion, the Qualtrics API does not respond reliably, if you have already checked your password or token and data center, try again in little while"'
error 6
}
mata: input = cat(`"`inpath'"') //Import file that was downloaded
qui getmata input //Make as variable
if regexm(`"`=input[1]'"',`"400 Bad Request: invalid header value"') {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Qualtrics returns "400 Bad Request: invalid header value". Check your token and data center or your password"'
error 198
}
************************Getting Multiple Pages if needed************************
************************Getting Multiple Pages if needed************************
************************Getting Multiple Pages if needed************************
local newurl
qui count if regexm(input,`""nextPage":"(https://.+offset=[0-9]+)""')
local renextpage `r(N)'
while `renextpage' == _N { //Change to while once I have it right
local newurl = regexs(1)
*di `"`newurl'"'
preserve
local inpath `"list.txt"' //Setting path to import API response
cap rm `"`inpath'"'
local url `"`newurl'"' //Url for this API call
if `"`method'"' == `"curl"' { // If curl
*Call to receive json file with surveys
!curl -H "`apiheader'" "`url'" -o "`inpath'"
}
if `"`method'"' == `"wget64"' { //if wget64
*Call to receive json file with surveys
!wget64 --header="`apiheader'" "`url'" -O "`inpath'"
}
clear
mata: input = cat(`"`inpath'"') //Import file that was downloaded
qui getmata input //Make as variable
tempfile newpage
qui sa `newpage'
restore
append using `newpage'
qui count if regexm(input,`""nextPage":"(https://.+offset=[0-9]+)""')
local renextpage `r(N)'
}
qui count
if `r(N)'>1 {
forvalues i = 1 / `r(N)' {
qui replace input = input + input[_n-1] if _n > 1
}
qui keep if _n == _N
}
**********************End Getting Multiple Pages if needed**********************
**********************End Getting Multiple Pages if needed**********************
**********************End Getting Multiple Pages if needed**********************
*****************Parsing out desired information from JSON file*****************
*****************Parsing out desired information from JSON file*****************
*****************Parsing out desired information from JSON file*****************
qui replace input=subinstr(input,`"id":"',`"~id":"',.)
qui split input, p("~")
qui drop input
qui gen n=1
qui reshape long input, i(n) j(line)
qui drop in 1
local getlist `"id name ownerId lastModified creationDate isActive"'
local gct=1
foreach gt of local getlist {
local ++gct
local stop `"`:word `gct' of `getlist''"'
cap drop `gt'
qui gen `gt'=regexs(1) if regexm(input,`"`gt'":"(.+)".+`stop'"')
}
qui replace isActive=regexs(1) if regexm(input,`""isActive":(.+)}"')
qui replace isActive=regexr(isActive,"}.+","")
bysort id: gen keep =_n==_N
qui keep if keep == 1
qui drop keep
***************End Parsing out desired information from JSON file***************
***************End Parsing out desired information from JSON file***************
***************End Parsing out desired information from JSON file***************
******************************List Subset Options*******************************
******************************List Subset Options*******************************
******************************List Subset Options*******************************
qui count
local total_surveys `r(N)'
if `total_surveys' == 0 {
if `restoredata' == 1 qui use `orginal_data', clear
if `restoredata' == 0 qui clear
set linesize `original_lines'
di as error `"No surveys found associated with your token and data center. Check your token and data center or your password"'
di as error `"On occasion, the Qualtrics API does not respond reliably, if you have already checked your password or token and data center, try again in little while"'
error 6
}
************************************RE Match************************************
************************************RE Match************************************
************************************RE Match************************************
if `"`match'"' !=`""' {
qui keep if regexm(name,`"`match'"')
}
**********************************End RE Match**********************************
**********************************End RE Match**********************************
**********************************End RE Match**********************************
*******************************(In)Active surveys*******************************
*******************************(In)Active surveys*******************************
*******************************(In)Active surveys*******************************
if `"`active'"' !=`""' {
if !inlist(trim(itrim(lower(`"`active'"'))),`"true"',`"false"') {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in Active option: Must specify either true or false"'
set linesize `original_lines'
error 198
}
qui keep if isActive == trim(itrim(lower(`"`active'"')))
}
*****************************End (In)Active surveys*****************************
*****************************End (In)Active surveys*****************************
*****************************End (In)Active surveys*****************************
******************************Modified Date Range*******************************
******************************Modified Date Range*******************************
******************************Modified Date Range*******************************
if `"`modrange'"' !=`""' {
if !regexm(`"`modrange'"',":") {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in MODRange option: Start Date and End Date must be separated by a colon(:)"'
set linesize `original_lines'
error 198
}
qui split lastModified, p(`"T"')
cap drop date
qui gen date= date(lastModified1,"YMD")
foreach loca in startdate enddate {
local `loca'
}
tokenize `"`modrange'"', p(`":"')
local startdate=date(`"`1'"',"MDY")
local enddate=date(`"`3'"',"MDY")
if `"`startdate'"' == `"."' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in MODRange option: Start Date not Valid, Enter a string in Month Day Year format"'
set linesize `original_lines'
error 198
}
if `"`enddate'"' == `"."' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in MODRange option: End Date not Valid, Enter a string in Month Day Year format"'
set linesize `original_lines'
error 198
}
if `startdate' > `enddate' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in MODRange option: Start Date is after End Date"'
set linesize `original_lines'
error 198
}
qui keep if inrange(date,`startdate',`enddate')
}
****************************End Modified Date Range*****************************
****************************End Modified Date Range*****************************
****************************End Modified Date Range*****************************
*******************************Created Date Range*******************************
*******************************Created Date Range*******************************
*******************************Created Date Range*******************************
if `"`createrange'"' !=`""' {
if !regexm(`"`createrange'"',":") {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in CREATERange option: Start Date and End Date must be separated by a colon(:)"'
set linesize `original_lines'
error 198
}
qui split creationDate, p(`"T"')
cap drop date
qui gen date= date(creationDate1,"YMD")
foreach loca in startdate enddate {
local `loca'
}
tokenize `"`createrange'"', p(`":"')
local startdate=date(`"`1'"',"MDY")
local enddate=date(`"`3'"',"MDY")
if `"`startdate'"' == `"."' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in CREATERange option: Start Date not Valid, Enter a string in Month Day Year format"'
set linesize `original_lines'
error 198
}
if `"`enddate'"' == `"."' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in CREATERange option: End Date not Valid, Enter a string in Month Day Year format"'
set linesize `original_lines'
error 198
}
if `startdate' > `enddate' {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Error in CREATERange option: Start Date is after End Date"'
set linesize `original_lines'
error 198
}
qui keep if inrange(date,`startdate',`enddate')
}
*****************************End Created Date Range*****************************
*****************************End Created Date Range*****************************
*****************************End Created Date Range*****************************
****************************End List Subset Options*****************************
****************************End List Subset Options*****************************
****************************End List Subset Options*****************************
**************************Making SMCL table of information and setting returned values*****************
qui count
local filtered_files `r(N)'
di as smcl `"{text}{hline 160}"'
di as result `"Found `total_surveys' surveys total"'
if `filtered_files' != `total_surveys' di as result `"`filtered_files' met the criteria specified"'
di as smcl `"{text}{hline 160}"'
local c1 80
local c2 100
local c3 124
local c4 146
di as smcl `"{result}name {col `c1'} surveyid {col `c2'} lastModified {col `c3'} creationDate {col `c4'} isActive"'
di as smcl `"{text}{hline 160}"'
gsort - lastModified
qui count
forvalues i=1/`r(N)' {
di as smcl `"{result}`=name[`i']' {col `c1'} `=id[`i']' {col `c2'} `=lastModified[`i']' {col `c3'} `=creationDate[`i']' {col `c4'} `=isActive[`i']'"'
return local key`i' `"`=id[`i']'"'
foreach rt of local getlist{
return local `rt'`i' `"`=`rt'[`i']'"'
}
return local filtered_surveys `"`filtered_files'"'
return local total_surveys `"`total_surveys'"'
}
di as smcl `"{text}{hline 160}"'
**********************END Making SMCL table of information and setting returned values****************
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
cap rm `"`inpath'"'
exit
*******************************************************End List Surveys Function********************************************************
*******************************************************End List Surveys Function********************************************************
*******************************************************End List Surveys Function********************************************************
}
*********************************************Get Surveys Function******************************************************
*********************************************Get Surveys Function******************************************************
*********************************************Get Surveys Function******************************************************
if `"`anything'"'==`"get"' { // if get function selected
*******************************Download Survey to csv**************************************************************
local surveyid `"`id'"' //Survey ID you want from here
local filetyp `"csv"' //file format to request
if `"`surveyid'"' == "" {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Must Specify survey ID using ID option"'
set linesize `original_lines'
error 198
}
if `"`csv'"'==`""' {
di as error `"must specify a location to save the csv, csv file name is provided by qualtrics"' //Need location for CSV, mostly because I think we should have one on hand
if `restoredata' == 1 | `"`preserve'"' != "" qui use `orginal_data', clear
set linesize `original_lines'
error 198
}
***Windows or Mac File Path
if `"`c(os)'"' == `"Windows"' {
local path=subinstr(`"`csv'"',"/","\",.) //Where data will be downloaded to
if substr(`"`path'"',-1,1) != `"\"' local path `"`path'\"'
local upath=subinstr(`"`csv'"',"\","/",.) //Where data will be downloaded to
if substr(`"`upath'"',-1,1) != `"/"' local upath `"`upath'/"'
}
if `"`c(os)'"' != `"Windows"' {
local path=`"`csv'"' //Where data will be downloaded to
if substr(`"`path'"',-1,1) != `"/"' local path `"`path'/"'
local upath=`"`csv'"' //Where data will be downloaded to
if substr(`"`upath'"',-1,1) != `"/"' local upath `"`upath'/"'
}
if `"`c(os)'"' == `"Windows"' {
if `"`valuelabs'"'==`""' local post `"{\"surveyId\": \"`surveyid'\", \"format\": \"`filetyp'\"}"' //JSON request for data
if `"`valuelabs'"'!=`""' local post `"{\"surveyId\": \"`surveyid'\", \"format\": \"`filetyp'\", \"useLabels\":true}"' //JSON request for data
}
if `"`c(os)'"' != `"Windows"' {
if `"`valuelabs'"'==`""' local post `"{"surveyId": "`surveyid'", "format": "`filetyp'"}"' //JSON request for data
if `"`valuelabs'"'!=`""' local post `"{"surveyId": "`surveyid'", "format": "`filetyp'", "useLabels":true}"' //JSON request for data
}
local inpath "`path'reqfile.txt" //This location for response to our request for a file
local url "https://`center'.qualtrics.com/API/v3/responseexports/" //url call for this process
if `"`method'"'==`"wget64"' {
cap rm `"`inpath'"'
*API request for file to be generated
!wget64 --post-data="`post'" --header="`apiheader'" --header="`headercont'" "`url'" -O "`inpath'"
clear
mata: input = cat(`"`inpath'"') //Importing file that contains information to check the progress of our request
getmata input
if regexm(`"`=input[1]'"',`"400 Bad Request: invalid header value"') {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Qualtrics returns "400 Bad Request: invalid header value". Check your token and data center or your password"'
error 198
}
gen request=regexs(1) if regexm(input, `""id":"(.+)"},"m"') //Extracting request ID to check progress
local request `"`=request[1]'"'
local retry=1
local tryct=0
while `retry'==1 &`tryct'<100 { //Repeat checking for the file to be ready until it is
sleep 1000 //wait a bit for file to be created
local ++tryct
local inpath "`path'progress.txt"
local url "https://`center'.qualtrics.com/API/v3/responseexports/"
*Call for file which tells us if the data is ready to download
!wget64 --header="`apiheader'" "`url'`request'" -O "`inpath'"
clear
mata: input = cat(`"`inpath'"')
getmata input
gen status=regexs(1) if regexm(input, `""status":"(.+)"},"m"') //extracting the status. i.e. complete or not
cap drop file
if `"`=status[1]'"'==`"complete"' { //if file is ready
local retry=0 //stop while loop
gen file=regexs(1) if regexm(input, `""file":"(.+)","s"') //get file url to download
}
if `tryct' == 100 {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"Qualtrics API not producing file. Check your token and data center or your password"'
di as error `"On occasion, the Qualtrics API does not respond reliably, if you have already checked your password or token and data center, try again in little while"'
error 6
}
} //End while retry==1
local file `"`=file[1]'"'
local inpath "`path'responses.zip"
cap rm `"`inpath'"'
*****Now download file
!wget64 --header="`headercont'" --header="`apiheader'" "`file'" -O "`inpath'"
} //end if wget64
if `"`method'"'==`"curl"' { //Except for the curl/wget commands, this is exactly the same as wget version
cap rm `"`inpath'"'
if `"`c(os)'"' == `"Windows"' {
!curl -X POST -H "`apiheader'" -H "`headercont'" -d "`post'" "`url'" -o "`inpath'"
}
if `"`c(os)'"' != `"Windows"' {
!curl -X POST -H "`apiheader'" -H "`headercont'" -d '`post'' "`url'" -o "`inpath'"
}
clear
local inpath "`path'reqfile.txt"
mata: input = cat(`"`inpath'"')
getmata input
if regexm(`"`=input[1]'"',`"400 Bad Request: invalid header value"') {
if `restoredata' == 1 qui use `orginal_data', clear
di as error `"Qualtrics returns "400 Bad Request: invalid header value". Check your token and data center or your password"'
error 198
}
gen request=regexs(1) if regexm(input, `""id":"(.+)"},"m"')
local request `"`=request[1]'"'
local retry=1
local tryct=0
while `retry'==1 &`tryct'<100 {
local ++tryct
local inpath "`path'progress.txt"
local url "https://`center'.qualtrics.com/API/v3/responseexports/"
!curl -H "`apiheader'" "`url'`request'" -o "`inpath'"
clear
mata: input = cat(`"`inpath'"')
getmata input
gen status=regexs(1) if regexm(input, `""status":"(.+)"},"m"')
cap drop file
if `"`=status[1]'"'==`"complete"' {
local retry=0
gen file=regexs(1) if regexm(input, `""file":"(.+)","s"')
}
if `tryct' == 100 {
if `restoredata' == 1 qui use `orginal_data', clear
set linesize `original_lines'
di as error `"Qualtrics API not producing file. Check your token and data center or your password"'
di as error `"On occasion, the Qualtrics API does not respond reliably, if you have already checked your password or token and data center, try again in little while"'
error 6
}
}
local file `"`=file[1]'"'
local inpath "`path'responses.zip"
!curl -X GET -H "`headercont'" -H "`apiheader'" "`file'" -o "`inpath'"
} // end if curl
local inpath "`path'responses.zip"
qui cd `"`path'"'
qui unzipfile `"`inpath'"', replace //unzip file
cap rm `"`path'reqfile.txt"'
cap rm `"`path'progress.txt"'
cap rm `"`path'responses.zip"'
di as smcl `"CLICK TO OPEN FOLDER: {browse `"`path'"'}"'
if `"`dta'"'==`""' {
cap {
timer off 13
qui timer list 13
local runtime `"`r(t13)'"'
return local runtime=`"`runtime'"'
}
}
*******************************END Download Survey to csv**********************************************************
**************************************Convert to DTA and apply labels, etc*********************************************
if `"`dta'"'!=`""' {
cap rm "`path'survey.txt" //Requesting download of all survey data in json, will use this to get csv name, and value labels, stems for labels
local inpath "`path'survey.txt"
local url "https://`center'.qualtrics.com/API/v3/surveys/`surveyid'"
if `"`method'"'==`"curl"' {
!curl -H "`apiheader'" "`url'" -o "`inpath'"
}
if `"`method'"'==`"wget64"' {
!wget64 --header="`apiheader'" "`url'" -O "`inpath'"
}
mata: input = cat(`"`inpath'"') //importing for name of survey
clear
mata: input = cat(`"`inpath'"')
getmata input
qui gen name=regexs(1) if regexm(input,`"name":"(.+)","ownerId"')
local file_name `"`=name[1]'"'
qui import delimited "`upath'`file_name'.csv", varn(1) case(lower) clear
if `"`clean'"'==`""' { //If clean option not selected just save the converted data and exit
sa `"`dta'"', replace
*cap rm "`path'survey.txt"
cap {
timer off 13
qui timer list 13
local runtime `"`r(t13)'"'
return local runtime=`"`runtime'"'
}
if `"`preserve'"' != "" qui use `orginal_data', clear
set linesize `original_lines'
exit
}
tempfile conv
qui sa `conv'
qui {
clear
mata: input = cat(`"`inpath'"') //reimporting the survey data
getmata input
qui count
if `r(N)'>1 { //Usually the json is all put in one line of code, but sometimes a newline character, probably in the survey text, causes it to be split, putting it back
forvalues i=2/`r(N)' {
qui qui replace input=input[1]+input[`i'] in 1
}
}
keep in 1
replace input=regexr(input,`"\\n"',"")
split input, p("QID")
drop input
gen n=1
reshape long input, i(n) j(line)
sum line if regexm(input,`""exportColumnMap""')
assert `r(N)'==1
drop if line>`r(mean)'
replace input=regexr(input,`""exportColumnMap.+""',"")
drop in 1
count if regexm(input,`""choices""')
gen subqs=1 if regexm(input,`""subQuestions""')|regexm(input,`""type":"MC","selector":"MA"')|regexm(input,`""type":"RO","selector"')|regexm(input,`""type":"CS","selector"')|regexm(input,`""type":"Matrix","selector"')
gen choices=regexs(0) if regexm(input,`""choices.+""')
replace choices="" if regexm(input,`""type":"MC","selector":"MA"')
gen checkboxes=1 if regexm(input,`""type":"MC","selector":"MA"')
replace choices="" if regexm(input,`""type":"RO","')
gen rank_order=1 if regexm(input,`""type":"RO","')
gen constant_sum=1 if regexm(input,`""type":"CS","')
gen matrix_qs = 1 if regexm(input,`""type":"Matrix","')
split choices, p(`""recode""')
drop choices
cap drop qid
gen qid=regexs(0) if regexm(input,"^[0-9]+")
replace qid="QID"+qid
local value_ct=0
ds choices*
foreach ch in `r(varlist)' {
local ++value_ct
gen num`value_ct'=regexs(0) if regexm(`ch',`"[0-9]+"')
gen choicetext`value_ct'=regexs(1) if regexm(`ch',`"choiceText":"(.+)","')
}
gen value_label="cap label define "+qid if !mi(choicetext1)
ds num*
foreach v in `r(varlist)' {
if regexm(`"`v'"',"[0-9]") {
local number=regexs(0)
}
replace value_label=value_label+" "+num`number'+`" ""'+choicetext`number'+`"" "' if !mi(choicetext`number')
}
replace value_label="" if regexm(value_label,"imageDescription")
list value_label
drop num* choicetext* choices*
cap drop stem
gen stem=regexs(1) if regexm(input,`"questionText":"(.+)",".+,"subQuestions"')
replace stem=regexs(1) if regexm(input,`""type":"MC","selector":"MA.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"Matrix","selector":".+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"RO",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"CS",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"SBS",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"TE",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"Slider",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"PGR",.+questionText":"(.+)","questionLabel"')
replace stem=regexs(1) if regexm(input,`""type":"HeatMap",.+questionText":"(.+)","questionLabel"')
gen subs=regexr(input,`"choices":.+"',"") if subqs==1&checkboxes!=1
replace subs=input if subqs==1&(checkboxes==1|rank_order==1|constant_sum==1) //matrix_qs
count if !mi(subs)
local hasubqs `r(N)'
if `hasubqs'>0 {
split subs,p("recode")
drop subs
ds subs*
local num=0
foreach s in `r(varlist)' {
if regexm(`"`s'"',"[0-9]+") {
local tnum=regexs(0)
}
if `tnum'>`num' local num `tnum'
}
di `tnum'
forvalues s=`tnum'(-1)2 {
local prior=`s'-1
gen subqn`s'=regexs(1) if regexm(subs`prior',`""([0-9]+)":{"$"')
}
drop n line
gen sort=_n
bysort qid: gen N=_N
qui sum N
if `r(max)'>1 {
egen keep=rownonmiss(value_label stem subs*), strok
bysort qid (keep): keep if _N==_n
drop keep
}
sort sort
drop sort N
reshape long subs subqn, i(qid) j(qnum)
gen subqt=regexs(1) if regexm(subs,`"choiceText":"(.+)","image"')
replace subqt=regexs(1) if regexm(subs,`"choiceText":"(.+)","')&mi(subqt)
*gen subqt=regexs(1) if regexm(subs,`"choiceText":"(.+)","')
drop if regexm(subs,"subQuestions")
drop subs qnum
}
cap drop question_name
gen question_name=qid
if `hasubqs'>0 replace question_name=question_name+`"_"'+subqn if !mi(subqn)
replace question_name=question_name+`"_TEXT"' if regexm(input,`"type":"TE""')
duplicates drop
if `hasubqs'>0 drop if mi(subqt)&subqs==1
gen quest_text=regexs(1) if regexm(input,`"questionText":"(.+)","questionLabel"')
if `hasubqs'>0 replace quest_text=subqt if !mi(subqt)
tempfile metan
sa `metan'
use `conv' in 2, clear
set obs `=_N+1'
foreach i of varlist * {
replace `i'=`"`i'"' in 2
}
qui sxpose, clear
qui replace _var1=subinstr(_var1,`"{'ImportId': '"',"",.)
qui replace _var1=subinstr(_var1,`"'}"',"",.)
qui replace _var1=subinstr(_var1,`"_"',"-",.)
qui split _var1, p("-")
qui rename _var11 question
qui rename _var12 subquest_num
qui replace subquest_num="" if subquest_num=="TEXT"
qui replace _var1=subinstr(_var1,"-","_",.)
qui count
local renct `r(N)'
forvalues i=1/`renct' {
local rct`i'
local rct`i' `"replace question_name=`"`=_var2[`i']'"' if question_name==`"`=_var1[`i']'"'"'
}
use `metan', clear
forvalues i=1/`renct' {
qui `rct`i''
}
ds, has(type string)
foreach v in `r(varlist)' {
while 1==1 {
gen start=strpos(`v',"<")
qui sum start
if `r(mean)'==0 {
drop start
continue, break
}
gen end=strpos(`v',">")
gen remove=substr(`v',start,end-start+1)
replace `v'=subinstr(`v',remove,"",1)
drop end start remove
}
replace `v'=regexr(`v',`"\\n"'," ")
}
cap drop n
cap drop line
qui gen n=_n
local value_lab_ct=0
qui levelsof qid if !mi(value_label), local(idlevs)
foreach qid of local idlevs {
local ++value_lab_ct
local val_lab`value_lab_ct'
local val_lab_app`value_lab_ct'
qui sum n if qid==`"`qid'"'
local val_lab`value_lab_ct' `"cap label define `qid' `=value_label[`r(min)']', replace"'
qui levelsof question_name if qid==`"`qid'"', clean
local val_lab_app`value_lab_ct' `"cap label values `r(levels)' `qid'"'
}
count
local varct `r(N)'
forvalues i=1/`varct' {
local qtext`i'
local qtext`i' `"cap char `=question_name[`i']'[stem] `"`=stem[`i']'"'"'
local qlab`i'
local qlab`i' `"cap label variable `=question_name[`i']' `"`=quest_text[`i']'"'"'
local qlabc`i'
local qlabc`i' `"cap char `=question_name[`i']'[full_text] `"`=quest_text[`i']'"'"'
}
use `conv', clear
foreach v of varlist * {
cap label variable `v' `"`=`v'[1]'"'
}
drop in 1/2
qui destring *, replace
foreach v of varlist * {
local ilist: char `v'[]
foreach i in `ilist' {
char `v'[`i']
}
}
forvalues i=1/`varct' {
`qlabc`i''
`qlab`i''
`qtext`i''
}
forvalues i=1/`value_lab_ct' {
`val_lab`i''
`val_lab_app`i''
}
}
sa `"`dta'"', replace //Save file
*cap rm `"`inpath'"' //remove survey file
di as smcl `"CLICK TO OPEN FOLDER: {browse `"`path'"'}"'
if `"`relab'"'!="" { //List out labels so can modify manually
se linesize 255
forvalues i=1/`varct' {
di `"`qlab`i''"'
}
}
if `"`revallab'"'!="" { //List out
forvalues i=1/`value_lab_ct' {
se linesize 255
di `"`val_lab`i''"'
di `"`val_lab_app`i''"'
}
}
}
}
cap {
timer off 13
qui timer list 13
local runtime `"`r(t13)'"'
return local runtime=`"`runtime'"'
}
if `"`preserve'"' != "" qui use `orginal_data', clear
end
-gtab- which stands for generalizable table, allows you to make completely custom tables. -tabout-,-estout- and many other output wrappers are great, but can't do everything and figuring out their syntax, and what they can and can't do can be time-consuming. What if you want to make a table of Cox Index effect sizes, or -ranksum- p-values, or want a very particular format or combination of information, including some text-for example- the list of variables included in that model? gtab allows you to define table columns, and then you can loop over variables and/or variable values and build the table that way. It then allows you to preview or export your table to excel. You can also run several gtab commands at once to construct multiple tables at the same time.
cap program drop gtab
program def gtab, nclass
local anything `0'
*********************************Command Sorter*********************************
*********************************Command Sorter*********************************
*********************************Command Sorter*********************************
tokenize `"`anything'"'
local tnum
local pos 1
if regexm(`"`1'"',`"^[0-9]+$"') {
local tnum=regexs(0)
local pos 2
}
*!DH Note 9/28/2020: Column names for initialize
if `"`tnum'"'=="" local pass_on=subinstr(`"`0'"',"`1'","",1)
if `"`tnum'"'!="" local pass_on=subinstr(`"`0'"',"`1' `2'","",1)
*!DH Note 9/28/2020: Initialize
if inlist(`"``pos''"',`"init"',`"initialize"') gtab_init `pass_on', pos(`pos') tnum(`tnum')
*!DH Note 9/28/2020: Add
if !inlist(`"``pos''"',"init","initialize","export","exp","pre","preview",`"imp"',`"import"', `"fill"') gtab_add `anything'
*!DH Note 9/28/2020: Fill
if `"``pos''"' == `"fill"' gtab_fill, pos(``pos'') tnum(`tnum')
*!DH Note 9/28/2020:preview
if inlist(`"``pos''"',"pre","preview") gtab_pre, tnum(`tnum')
*!DH Note 9/28/2020: import
if inlist(`"``pos''"',"imp","import") gtab_imp, pos(``pos'') tnum(`tnum')
*!DH Note 9/28/2020: export
if inlist(`"``pos''"',"exp","export") {
*!DH Note 9/28/2020: split file name from options
qui tokenize `"`pass_on'"', p(`","')
gtab_exp `1', pos(exp) tnum(`tnum') `3'
}
*******************************End Command Sorter*******************************
*******************************End Command Sorter*******************************
*******************************End Command Sorter*******************************
end
**************************Initialize columns for table***************************
**************************Initialize columns for table***************************
**************************Initialize columns for table***************************
cap program drop gtab_init
program def gtab_init, nclass
syntax anything, [ ///
pos(string) /// Position of subcommand
tnum(string)] //Table number
global dhtbl`tnum'_cols ""
local columns`tnum'=trim(itrim(`"`anything'"'))
local badcol = 0
local badcollist ""
*!DH Note 9/18/2020: Checking for columns named with words reserved for sub-functions
foreach c in `columns`tnum''{
foreach no in init initialize export exp pre preview import imp fill {
if `"`c'"' == `"`no'"' {
local ++badcol
local badcollist `badcollist' `c'
}
}
}
if `badcol' > 0 {
local plural
local plural2 an
if `badcol' > 1 {
local plural s
local plural2
}
di as error `"`badcol' specified column`plural' reserved for sub-commands (`badcollist'). Please choose `plural2'other name`plural'."'
di as error `"initialize, init, export, exp, preview, pre, import, imp, and fill are reserved"'
error 198
}
*!DH Note 9/18/2020: Duplicated column names
local uniquecs
if `"`columns`tnum''"' != `"`:list uniq columns`tnum''"' {
di as error `"All column names must be unique, the following are repeated: `:list dups columns`tnum''."'
error 198
}
di as result `"table initialized with columns `columns`tnum''"'
global dhtbl`tnum'_cols `"`columns`tnum''"'
global dhtbl`tnum'_cols2 ""
tokenize `columns`tnum''
local cct=0
while `"`*'"'!="" {
local ++cct
global dhtbl`tnum'_`cct' `"`1'"'
global dhtbl`tnum'_ct_`cct'=0
global dhtbl`tnum'_cols2 "${dhtbl`tnum'_cols2} dhtbl`tnum'_`cct'"
di `"`=itrim(`"gtab `tnum' ${dhtbl`tnum'_`cct'}"')'"'
macro shift
}
end
************************End Initialize columns for table*************************
************************End Initialize columns for table*************************
************************End Initialize columns for table*************************
******************************Add text to columns*******************************
******************************Add text to columns*******************************
******************************Add text to columns*******************************
cap program drop gtab_add
program def gtab_add, nclass
*!DH Note 9/28/2020: I can't use syntax to pass more information because stata will assume any , in table text indicates options, so I need to manually re-parse the command for this one
local anything `"`0'"'
tokenize `"`anything'"'
local tnum
local pos 1
if regexm(`"`1'"',`"^[0-9]+$"') {
local tnum=regexs(0)
local pos 2
}
if `"`tnum'"'=="" local coln `"`1'"'
if `"`tnum'"'!="" local coln `"`2'"'
local nonm=0
local cct=0
foreach c of global dhtbl`tnum'_cols {
local ++cct
if `"`c'"' == `"`coln'"' {
local nonm=1
if `"`tnum'"'=="" local text=trim(itrim(subinstr(`"`anything'"',"`1'","",1)))
if `"`tnum'"'!="" local text=trim(itrim(subinstr(`"`anything'"',"`1' `2'","",1)))
if ${dhtbl`tnum'_ct_`cct'} > 0 {
mata: dhtbl`tnum'_`cct'=dhtbl`tnum'_`cct'\(`"`text'"')
global dhtbl`tnum'_ct_`cct'= ${dhtbl`tnum'_ct_`cct'}+1
}
if ${dhtbl`tnum'_ct_`cct'} == 0 {
mata: dhtbl`tnum'_`cct'=(`"`text'"')
global dhtbl`tnum'_ct_`cct'= ${dhtbl`tnum'_ct_`cct'}+1
}
continue, break
}
}
if `nonm' == 0 {
if `"`tnum'"'=="" di as error `"`1' is not an initialized column"'
if `"`tnum'"'!="" di as error `"`2' is not an initialized column for table `tnum'"'
error 198
}
end
****************************End Add text to columns*****************************
****************************End Add text to columns*****************************
****************************End Add text to columns*****************************
*******************************Fill partial rows********************************
*******************************Fill partial rows********************************
*******************************Fill partial rows********************************
cap program drop gtab_fill
program def gtab_fill, nclass
syntax, [ ///
pos(string) /// subcommand
tnum(string)] //Table number
gtab_check, tnum(`tnum') pos(fill)
end
*****************************End Fill partial rows******************************
*****************************End Fill partial rows******************************
*****************************End Fill partial rows******************************
*********************************Preview Table**********************************
*********************************Preview Table**********************************
*********************************Preview Table**********************************
cap program drop gtab_pre
program def gtab_pre, nclass
syntax, [ ///
tnum(string)] //Table number
gtab_check, tnum(`tnum') pos(pre)
local mct = 0
foreach c of global dhtblO`tnum'_cols2 {
local ++mct
if `mct' == 1 mata: dhbtlresult`tnum' = (`c')
if `mct' > 1 mata: dhbtlresult`tnum' = dhbtlresult`tnum',(`c')
}
mata:dhbtlresult`tnum'
mata: mata drop dhbtlresult`tnum'
foreach c of global dhtblO`tnum'_cols2 {
mata: mata drop `c'
}
global dhtblO`tnum'_cols2
end
*******************************End Preview Table********************************
*******************************End Preview Table********************************
*******************************End Preview Table********************************
************************Import Table into Stata Dataset*************************
************************Import Table into Stata Dataset*************************
************************Import Table into Stata Dataset*************************
cap program drop gtab_imp
program def gtab_imp, nclass
syntax, [ ///
pos(string) ///subcommand
tnum(string)] //Table number
gtab_check, tnum(`tnum') pos(imp)
clear
foreach c of global dhtbl`tnum'_cols2 {
getmata (`c') = `c'
}
local ct=0
qui ds dhtbl*
foreach v in `r(varlist)' {
local ++ct
rename `v' column_`ct'
}
foreach c of global dhtbl`tnum'_cols2 {
mata: mata drop `c'
}
macro drop dhtbl`tnum'_*
end
**********************End Import Table into Stata Dataset***********************
**********************End Import Table into Stata Dataset***********************
**********************End Import Table into Stata Dataset***********************
*****************************Export Table to Excel******************************
*****************************Export Table to Excel******************************
*****************************Export Table to Excel******************************
cap program drop gtab_exp
program def gtab_exp, nclass
syntax anything, [ ///
pos(string) ///subcommand
tnum(string) ///Table number
replace /// replace excel file
sheetreplace /// replace sheet
sheetmodify /// Modify sheet
sheet(string) ///Which Sheet
cell(string)] //Which cell to start in
if `"`sheet'"' == "" local sheet `"Sheet1"'
*!DH Note 9/29/2020: Making sure only one of these is specified
local sheetops = 0
foreach op in replace sheetreplace sheetmodify {
if `"``op''"' != "" local ++sheetops
}
if `sheetops'>1 {
di as error`" May only specify one of replace, sheetreplace, and sheetmodify"'
error 198
}
gtab_check, tnum(`tnum') pos(exp)
local mct = 0
foreach c of global dhtbl`tnum'_cols2 {
local ++mct
if `mct' == 1 mata: dhbtlresult`tnum' = (`c')
if `mct' > 1 mata: dhbtlresult`tnum' = dhbtlresult`tnum',(`c')
}
local fname = subinstr(`"`anything'"',".xlsx","",1)
local fname = subinstr(`"`fname'"',".xls","",1)
if `"`cell'"' == "" local ex_cell `"1,1"'
if `"`cell'"' != "" {
local ex_cell `"`=upper(`"`cell'"')'"'
if !regexm(`"`ex_cell'"',`"^([A-Z]+)([1-9]+)$"') {
di as error `"Please specify cell as [A-Z]+[1-9]+ format"'
error 198
}
if regexm(`"`ex_cell'"',`"^([A-Z]+)([1-9]+)$"') {
local lets = regexs(1)
local numbs = regexs(2)
*!DH Note 9/29/2020: letter list that follow A-Z AA-ZZ, AAA-ZZZ
local letlist `c(ALPHA)'
if `=length(`"`lets'"')' > 1 {
foreach f in `c(ALPHA)' {
foreach s in `c(ALPHA)' {
local letlist `letlist' `f'`s'
}
}
}
*!DH Note 10/2/2020: This adds a ton of time, only add these if the selected column uses 3 letters
if `=length(`"`lets'"')' > 2 {
foreach f in `c(ALPHA)' {
foreach s in `c(ALPHA)' {
foreach th in `c(ALPHA)' {
local letlist `letlist' `f'`s'`th'
}
}
}
}
local lct=0
foreach l in `letlist' {
local ++lct
if `"`lets'"' == `"`l'"' {
local let_num = `lct'
continue, break
}
}
local ex_cell `"`numbs',`let_num'"'
}
}
*!DH Note 11/11/2020: Determining if the file/sheet exists
cap confirm file `"`fname'.xlsx"'
if _rc == 0 local f_exists 1
if _rc != 0 local f_exists 0
local s_exists 0
local onesheet 0
if `f_exists' == 1 {
qui import excel `"`fname'.xlsx"', desc
if `r(N_worksheet)' == 1 local onesheet 1
forvalues i = 1/`r(N_worksheet)' {
if `"`r(worksheet_`i')'"' == `"`sheet'"' local s_exists 1
}
}
if (`"`replace'"' != `""' | `"`sheetreplace'"' != `""' | `"`sheetmodify'"' != `""') & `f_exists' == 0 di as smcl `"Note: `fname'.xlsx not found"'
*!DH Note 9/29/2020: If no replace/modify option passed
if (`"`replace'"' == `""' & `"`sheetreplace'"' == `""' & `"`sheetmodify'"' == `""') | `f_exists' == 0 {
mata: gtabb=xl()
mata: gtabb.create_book(`"`fname'"',`"`sheet'"')
mata: gtabb.put_string(`ex_cell',dhbtlresult`tnum')
di as smcl `"CLICK TO OPEN: {browse `"`fname'.xlsx"'}"'
}
if (`"`replace'"' == `"replace"'|(`"`sheetreplace'"' == `"sheetreplace"' & `onesheet' == 1 & `s_exists' == 1)) & `f_exists' == 1 {
cap rm `"`fname'.xlsx"'
if _rc != 0 {
di as error `"Unable to remove existing file, may be open or otherwise locked"'
}
mata: gtabb=xl()
mata: gtabb.create_book(`"`fname'"',`"`sheet'"')
mata: gtabb.put_string(`ex_cell',dhbtlresult`tnum')
di as smcl `"CLICK TO OPEN: {browse `"`fname'.xlsx"'}"'
}
if `"`sheetreplace'"' == `"sheetreplace"' & `f_exists' == 1 & (`onesheet' == 0 | (`onesheet' == 1 & `s_exists' == 0)) {
if `s_exists' == 1 {
mata:repex(`"`fname'"',`"`sheet'"',`ex_cell',dhbtlresult`tnum')
}
if `s_exists' == 0 {
mata:repnew(`"`fname'"',`"`sheet'"',`ex_cell',dhbtlresult`tnum')
di as smcl `"Note `sheet' did not exist in excel file"'
}
di as smcl `"CLICK TO OPEN: {browse `"`fname'.xlsx"'}"'
}
if `"`sheetmodify'"' == `"sheetmodify"' & `f_exists' == 1 {
if `s_exists' == 0 {
mata:repnew(`"`fname'"',`"`sheet'"',`ex_cell',dhbtlresult`tnum')
di as smcl `"Note: Sheet `sheet' did not exist in excel file"'
}
if `s_exists' == 1 {
mata:modex(`"`fname'"',`"`sheet'"',`ex_cell',dhbtlresult`tnum')
}
di as smcl `"CLICK TO OPEN: {browse `"`fname'.xlsx"'}"'
}
macro drop dhtbl`tnum'_*
end
***************************End Export Table to Excel****************************
***************************End Export Table to Excel****************************
***************************End Export Table to Excel****************************
*******************Mata Functions used for exporting to excel*******************
*******************Mata Functions used for exporting to excel*******************
*******************Mata Functions used for exporting to excel*******************
mata:
/*Sheet replace when that sheet does not exist*/
void repnew(string scalar fname,string scalar sheet, real scalar rown, real scalar coln, string matrix tablen)
{
class xl scalar gtabb
gtabb=xl()
gtabb.load_book(fname)
gtabb.add_sheet(sheet)
gtabb.set_sheet(sheet)
gtabb.put_string(rown,coln,tablen)
}
/*Sheet replace when that sheet does exist*/
void repex(string scalar fname,string scalar sheet, real scalar rown, real scalar coln, string matrix tablen)
{
class xl scalar gtabb
gtabb=xl()
gtabb.load_book(fname)
gtabb.delete_sheet(sheet)
gtabb.add_sheet(sheet)
gtabb.set_sheet(sheet)
gtabb.put_string(rown,coln,tablen)
}
/*Sheet modify when that sheet does exist*/
void modex(string scalar fname,string scalar sheet, real scalar rown, real scalar coln, string matrix tablen)
{
class xl scalar gtabb
gtabb=xl()
gtabb.load_book(fname)
gtabb.set_sheet(sheet)
gtabb.put_string(rown, coln, tablen)
}
end
*****************End Mata Functions used for exporting to excel*****************
*****************End Mata Functions used for exporting to excel*****************
*****************End Mata Functions used for exporting to excel*****************
********************Checks/Prepares vectors for imp pre exp*********************
********************Checks/Prepares vectors for imp pre exp*********************
********************Checks/Prepares vectors for imp pre exp*********************
cap program drop gtab_check
program def gtab_check, nclass
syntax, [ ///
pos(string) /// subcommand using this function
tnum(string)] //Table number
*!DH Note 7/30/2020: Stop if already imported or exported
if `"${dhtbl`tnum'_cols}"' == "" {
di as error `"gtab `tnum' not initialized (or already imported or exported)"'
error 198
}
local ok=1
local rowmax=0
local cols `:word count ${dhtbl`tnum'_cols2}'
forvalues j=1/`cols' {
if `rowmax'< ${dhtbl`tnum'_ct_`j'} local rowmax= ${dhtbl`tnum'_ct_`j'}
}
forvalues j=1/`cols' {
if ${dhtbl`tnum'_ct_`j'} != `rowmax' local ok=0
}
*!DH Note 9/28/2020: if import or export, modify main matrix for table
*!DH Note 9/28/2020: if preview, generate new vectors whose lengths are equalized so that you can preview without equalizing main table vectors
if `"`pos'"' == `"pre"' {
global dhtblO`tnum'_cols2
forvalues i=1/`cols' {
mata: dhtblO`tnum'_`i'=dhtbl`tnum'_`i'
global dhtblO`tnum'_cols2 ${dhtblO`tnum'_cols2} dhtblO`tnum'_`i'
}
if `ok'!=1 {
di as error `"Table columns do not have same number of rows!"'
di as error `"Table may be mis-aligned!"'
forvalues i=1/`cols' {
di as result `"${dhtbl`tnum'_`i'}:${dhtbl`tnum'_ct_`i'} Rows"'
if ${dhtbl`tnum'_ct_`i'} < `rowmax' {
local reps=`rowmax'-${dhtbl`tnum'_ct_`i'}
if `reps'> 0 {
forvalues r=1/`reps' {
if ${dhtbl`tnum'_ct_`i'} > 0 mata: dhtblO`tnum'_`i'=dhtblO`tnum'_`i'\(`""')
if ${dhtbl`tnum'_ct_`i'} == 0 mata: dhtblO`tnum'_`i'=(`""')
}
}
}
}
}
}
if inlist(`"`pos'"',`"imp"',`"exp"',`"fill"') {
if `ok'!=1 {
if inlist(`"`pos'"',`"imp"',`"exp"') {
di as error `"Table columns do not have same number of rows!"'
di as error `"Table may be mis-aligned!"'
}
forvalues i=1/`cols' {
if inlist(`"`pos'"',`"imp"',`"exp"') di as result `"${dhtbl`tnum'_`i'}:${dhtbl`tnum'_ct_`i'} Rows"'
if ${dhtbl`tnum'_ct_`i'} < `rowmax' {
local reps=`rowmax'-${dhtbl`tnum'_ct_`i'}
if `reps'> 0 {
forvalues r=1/`reps' {
if ${dhtbl`tnum'_ct_`i'} > 0 mata: dhtbl`tnum'_`i'=dhtbl`tnum'_`i'\(`""')
if ${dhtbl`tnum'_ct_`i'} == 0 mata: dhtbl`tnum'_`i'=(`""')
global dhtbl`tnum'_ct_`i'= ${dhtbl`tnum'_ct_`i'}+1
}
}
}
}
}
}
end
******************End Checks/Prepares vectors for imp pre exp*******************
******************End Checks/Prepares vectors for imp pre exp*******************
******************End Checks/Prepares vectors for imp pre exp*******************
-fmtexl- is a wrapper for -putexcel- to apply formatting to excel tables. You can change various aspects, including the number of label columns and header rows, colors, etc. and it will apply that formatting to tables. By default, it will loop over the sheets in an excel file and format them all, but you can also specify a specific sheet and/or a cell range within each sheet. The bw option is pretty close to journal style tables.
cap program drop fmtexl
program define fmtexl, rclass
syntax using/ [, Sheet(string) /// For formatting only 1 sheet of file, default is all
HEADFill(string) /// Header fill color, default is Gibson blue
HEADText(string) /// Header text color, default is white
LFill(string) /// Light row fill color, default is white
DFill(string) /// Dark row fill color, default is light blue
ROWcol /// Table body row alternating coloring
DOUBlerowcol /// Alternates colors every 2 rows rather than 1
Borders /// adds borders to all cells
HEADRows(integer 1) ///More than 1 header row
LABCols(integer 1) ///More than 1 label column
SPECRange(string) ///Specify table cell range rather then detect from import excel, formatted as [A-Z]*[1-9]*:[A-Z]*[1-9]*
BW ///Black and white journal style table, don't specify other formatting if you do so, will overwrite
]
global shnamelist //Clear lists from possible previous runs
global shrangelist
if `"`headfill'"'==`""' local headfill "020 065 092" //Set defaults for colors
if `"`headtext'"'==`""' local headtext "white"
if `"`lfill'"'==`""' local lfill "white"
if `"`dfill'"'==`""' local dfill "228 238 248"
local torest = 0
qui count
if `r(N)' != 0 {
local torest = 1
tempfile fmtpre
sa `fmtpre'
clear
}
if `"`bw'"'!="" {
local headfill "white" //Set defaults for colors
local headtext "black"
local lfill "white"
local dfill "white"
}
qui import excel `"`using'"', describe //Get list of sheets and cell ranges
******Setting up to cycle through each sheet in a excel workbook unless a sheet is specified
*******If only one worksheet
if `r(N_worksheet)'==1 {
global shnamelist `"`"`r(worksheet_1)'"'"'
if `"`specrange'"'==`""' global shrangelist `"`r(range_1)'"'
if `"`specrange'"'!=`""' global shrangelist `"`specrange'"'
}
*******If multiple worksheets
if `r(N_worksheet)'>1 {
forvalues i=1/`r(N_worksheet)' {
global shnamelist `"${shnamelist} `"`r(worksheet_`i')'"'"'
if `"`specrange'"'==`""' global shrangelist `"${shrangelist} `r(range_`i')'"'
if `"`specrange'"'!=`""' global shrangelist `"${shrangelist} `specrange'"'
}
}
*******If a specfic sheet has been specified
if `"`sheet'"'!="" {
local shtct=0
foreach sht of global shnamelist { //Loops over each sheet name to look for the specified sheet and get its range
local ++shtct
local range: word `shtct' of ${shrangelist}
local foundsheet=0
if `"`sht'"'==`"`sheet'"' {
local foundsheet=1
global shnamelist `"`"`sht'"'"'
if `"`specrange'"'==`""' global shrangelist `"`range'"'
if `"`specrange'"'!=`""' global shrangelist `"`specrange'"'
}
}
if `foundsheet'==0 {
di in red `"Sheet "`sheet'" not found in `using'"' // If specified sheet is not in workbook
exit
}
}
local shtct=0
foreach sht of global shnamelist {
local ++shtct
local range: word `shtct' of ${shrangelist}
putexcel set `"`using'"', modify sheet("`sht'")
*****Getting cell ranges for formatting commands
if regexm(`"`range'"',`"^([A-Z]+[0-9]+):([A-Z]+[0-9]+)$"') {
local topleft=regexs(1)
local bottomright=regexs(2)
*di `"topleft: `topleft'"'
*di `"bottomright: `bottomright'"'
}
if regexm(`"`topleft'"',`"[A-Z]+"') local leftcol=regexs(0)
if regexm(`"`topleft'"',`"[0-9]+"') local toprow=regexs(0)
if regexm(`"`bottomright'"',`"[A-Z]+"') local rightcol=regexs(0)
if regexm(`"`bottomright'"',`"[0-9]+"') local bottomrow=regexs(0)
**********Getting second row and column
****Second row (or first non-header row
local row2=`toprow'+`headrows'
****Second column or first non-label column
*******If left column is A-Z
if length(`"`leftcol'"')==1 {
local act=0
foreach i in `c(ALPHA)' {
local ++act
if `"`i'"'==`"`leftcol'"'&`"`leftcol'"'!=`"Z"' local col2 `"`:word `=`act'+`labcols'' of `c(ALPHA)''"'
if `"`leftcol'"'==`"Z"'&`labcols'==1 local col2 `"A`: word `=`labcols'+1' of `c(ALPHA)''"'
}
}
/* REsume here.
*!DH Note 4/7/2020: setting column widths
import excel using `"`using'"', sheet("`sht'")
mata:b=xl()
mata:b.load_book("${writeto}student_list_`dname'.xlsx")
mata:b.set_sheet(`"instructions"')
mata:b.set_mode(`"open"')
mata:b.set_column_width(1,1,20)
mata:b.set_column_width(2,2,100)
mata:b.set_row_height(6,6,70)
mata:b.set_row_height(12,12,0)
mata:b.close_book()
*/
*******If left column is AA-AY
if length(`"`leftcol'"')>1 {
local act=0
local letlen=`=length(`"`leftcol'"')'-1
local firstlets=substr(`"`leftcol'"',1,`letlen')
local lastlet=substr(`"`leftcol'"',-1,.)
if `"`lastlet'"'!=`"Z"' {
foreach i in `c(ALPHA)' {
local ++act
if `"`i'"'==`"`lastlet'"' {
local l2 `"`:word `=`act'+`labcols'' of `c(ALPHA)''"'
local col2 `"`firstlets'`l2'"'
}
}
}
if `"`lastlet'"'==`"Z"' {
foreach i in `c(ALPHA)' {
local ++act
if `"`i'"'==`"`firstlets'"' {
local l2 `"`:word `=`act'+1' of `c(ALPHA)''"'
local col2 `"`l2'`:word `labcols' of `c(ALPHA)''"'
}
}
}
}
*di `"Range: `range'"'
*di `"row2: `row2'"'
*di `"col2: `col2'"'
if `headrows'==1 {
if `"`bw'"'=="" {
qui putexcel `leftcol'`toprow':`rightcol'`toprow', hcenter font(Calibri,11,`headtext') bold
qui putexcel `leftcol'`toprow':`rightcol'`toprow', fpattern(solid,"`headfill'")
}
if `"`bw'"'!="" {
qui putexcel `leftcol'`toprow':`rightcol'`toprow', hcenter font(Calibri,11,`headtext')
qui putexcel `leftcol'`toprow':`rightcol'`toprow', border(bottom)
}
}
if `headrows'>1 {
if `"`bw'"'=="" {
qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', hcenter font(Calibri,11,`headtext') bold
qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', fpattern(solid,"`headfill'")
}
if `"`bw'"'!="" {
qui putexcel `leftcol'`toprow':`rightcol'`=`toprow'+`headrows'-1', hcenter font(Calibri,11,`headtext')
qui putexcel `leftcol'`=`toprow'+`headrows'-1':`rightcol'`=`toprow'+`headrows'-1', border(bottom)
}
}
if `headrows'==1 qui putexcel `col2'`toprow':`rightcol'`toprow', txtwrap
if `headrows'>1 qui putexcel `col2'`toprow':`rightcol'`=`toprow'+`headrows'', txtwrap
if `labcols'==1 qui putexcel `leftcol'`toprow':`leftcol'`bottomrow', left
if `labcols'>1 qui putexcel `leftcol'`toprow':`col2'`bottomrow', left
if `headrows'==1 qui putexcel `col2'`toprow':`rightcol'`bottomrow', hcenter
if `headrows'>1 qui putexcel `col2'`row2':`rightcol'`bottomrow', hcenter
if `"`borders'"'!="" qui putexcel `topleft':`bottomright', border(all)
if `"`rowcol'"'!=`""' {
local lrows ""
local drows ""
if `"`doublerowcol'"'==`""' {
forvalues i=`row2'/`bottomrow' {
if `"`=mod(`i',2)'"'=="0" local drows `"`drows' `leftcol'`i':`rightcol'`i'"'
if `"`=mod(`i',2)'"'!="0" local lrows `"`lrows' `leftcol'`i':`rightcol'`i'"'
}
}
if `"`doublerowcol'"'!=`""' {
global color "white"
local cct=0
forvalues i=`row2'/`bottomrow' {
local ++cct
global lcolor "${color}"
if `"${lcolor}"'=="${myltblue}" local drows `"`drows' `leftcol'`i':`rightcol'`i'"'
if `"${lcolor}"'=="white" local lrows `"`lrows' `leftcol'`i':`rightcol'`i'"'
if `cct'==2 {
local cct 0
if `"${lcolor}"'=="white" global color `"${myltblue}"'
if `"${lcolor}"'=="${myltblue}" global color `"white"'
}
}
}
qui putexcel `drows', fpattern(solid, "`dfill'")
qui putexcel `lrows', fpattern(solid, "`lfill'")
}
if `"`bw'"'!=`""' qui putexcel `topleft':`bottomright', fpattern(solid, "white")
di `"`sht' formatted (table range `range')"'
}
di as smcl `"CLICK TO OPEN: {browse `"`using'"'}"'
local torest = 0
if `torest' == 1 {
use `fmtpre', clear
}
Anyone who has ever done an append in Stata has tried to run an append, gotten and error that variable x is format byte in using, but string in master. The thing is, Stata stops after the first problem and so you have to try again and again. No longer, -apchx- checks for type inconsistencies and stores those in a returned macro. It can also check if variable ranges are the same and if the value labels are the same (the name of the value label and the particular labels assigned to each value).
cap program drop apchx
program define apchx, rclass
syntax using/ [, ///
Label /// Check value label consistency
Range /// Check value range consistency
]
cap drop __0*
local switchback=0
if `"`c(varabbrev)'"' == `"on"' {
set varabbrev off
local switchback=1
}
local vlist ""
foreach check in type `range' `label' {
local `check'list ""
local `check'ct=0
}
local allmissmain
local allmissuse
local vct=0
foreach var of varlist * {
local ++vct
************************Check type
local v`vct'n `var'
local v`vct't: type `var'
if substr(`"`v`vct't'"',1,3)=="str" {
local v`vct't "str"
local v`vct'td "string"
}
if substr(`"`v`vct't'"',1,3)!="str" {
local v`vct't "num"
local v`vct'td "numeric"
}
****Check range
local v`vct'mn
local v`vct'mx
if `"`range'"'!=`""'&!regexm(`"`:type `v`vct'n''"',"str") {
qui count if !mi(`v`vct'n')
if `r(N)'!=0 {
qui sum `v`vct'n'
local v`vct'mn=`r(min)'
local v`vct'mx=`r(max)'
}
if `r(N)'==0 {
local allmissmain `"`allmissmain' `v`vct'n'"'
}
}
****Check value label
local v`vct'va
if `"`label'"'!=`""'&`"`:value label `v`vct'n''"'!=`""' {
local v`vct'va `"`:value label `v`vct'n''"'
local mnlb=100000000
local mxlb=-100000000
foreach v of varlist * {
if `"`:value label `v''"'==`"`v`vct'va'"' {
qui sum `v'
if !inlist(`r(N)',0,.) {
if `r(min)'<`mnlb' local mnlb=`r(min)'
if `r(max)'>`mxlb' local mxlb=`r(max)'
}
}
}
local v`vct'mnlb `mnlb'
local v`vct'mxlb `mxlb'
forvalues lb=`v`vct'mnlb'/`v`vct'mxlb' {
local v`vct'lb`lb' `"`:label (`var') `lb''"'
}
}
local vlist "`vlist' `var'"
}
tempfile main
qui sa `main'
qui use `"`using'"', clear
cap drop __0*
forvalues c=1/`vct' {
local var `v`c'n'
cap confirm variable `var'
if _rc==0 {
****************Check type
local typ: type `var'
if substr(`"`typ'"',1,3)=="str" {
local typ "str"
local typd "string"
}
if substr(`"`typ'"',1,3)!="str" {
local typ "num"
local typd "numeric"
}
if `"`typ'"'!=""&`"`typ'"'!=`"`v`c't'"' {
di as error `"`var' is `v`c'td' in main dataset and `typd' in the dataset to append"'
local ++typect
local typelist "`typelist' `var'"
}
*****Check ranage
if `"`range'"'!=`""'&!regexm(`"`:type `var''"',"str")&!regexm(`"`v`c't'"',`"str"') {
qui count if !mi(`var')
if `r(N)'!=0&!regexm(`"`allmissmain'"',`"`var'"') {
qui sum `var'
if `r(min)'!=`v`c'mn'|`r(max)'!=`v`c'mx' {
di as error `"`var' has range of `v`c'mn' to `v`c'mx' in main dataset and `r(min)' to `r(max)' in the dataset to append"'
local rangelist `"`rangelist' `var'"'
local ++rangect
}
}
if `r(N)'==0 {
local allmissuse `"`allmissuse' `var'"'
}
}
**Check value labels
if `"`label'"'!=`""'&!regexm(`"`:type `var''"',"str")&!inlist(`"`:value label `var''"',`""',`"`v`c'va'"') {
di as error `"`var' has `v`c'va' value label in main dataset and `:value label `var'' in the dataset to append"'
local labellist `"`labellist' `var'"'
local ++labelct
}
if `"`label'"'!=`""'&`"`:value label `var''"'!=`""'&inlist(`"`:value label `var''"',`"`v`c'va'"') {
forvalues lb=`v`c'mnlb'/`v`c'mxlb' {
if `"`v`c'lb`lb''"'!=`"`:label (`var') `lb''"' {
di as error `"`var' value label (`v`c'va') value `lb' is "`v`c'lb`lb''" in main dataset and "`:label (`var') `lb''" in the dataset to append"'
if !regexm(`" `labellist' "',`" `var' "') local labellist `"`labellist' `var'"'
local ++labelct
}
}
}
}
}
qui use `main', clear
if `switchback' == 1 {
set varabbrev on
}
foreach check in type `range' `label' {
return local `check'_mismatch=`"``check'list'"'
if ``check'ct'==0 di as result `"No `check' inconsistencies found"'
if ``check'ct'==1 di as error `"1 `check' inconsistency found"'
if ``check'ct'>1 di as error `"``check'ct' `check' inconsistencies found"'
}
local allmissmain=trim(`"`allmissmain'"')
local allmissuse=trim(`"`allmissuse'"')
if `"`allmissmain'"'!=`""' di as error `"`allmissmain' are all missing in master dataset"'
if `"`allmissuse'"'!=`""' di as error `"`allmissuse' are all missing in using dataset"'
-charlist- by Nick Cox is great for seeing all of the characters in a variable, but sometimes the order matters. -char_finder- instead takes a string (rather than a variable) as an argument and displays both each character and the ASCII code for it.
cap program drop char_finder
program define char_finder, rclass
syntax anything
local temp `anything'
local strl=length(`"`temp'"')
local inp `"`temp'"'
se linesize 200
local strct=0
local lets
local chars
forvalues p=1/`strl' {
local ++strct
local curlet=substr(`"`inp'"',`p',1)
local char
forvalues i=1/255 {
if `i'!=96 {
if `"`=char(`i')'"'==`"`curlet'"' {
local char `i'
continue, break
}
}
}
if `strct'<40 {
local lets `"`lets' {col `=`strct'*4'} `curlet'"'
local chars`"`chars' {col `=`strct'*4'}`char'"'
}
if `strct'>=40 {
local lets `"`lets' {col `=`strct'*4'} `curlet'"'
local chars`"`chars' {col `=`strct'*4'} `char'"'
di as smcl `"`lets'"'
di as smcl `"`chars'"'
di as smcl `"{hline 160}"'
local strct=0
local lets
local chars
}
}
di as smcl `"`lets'"'
di as smcl `"`chars'"'
di as smcl `"{hline 160}"'
Tries to restore syntax from your temporary folder. Each time you execute a do command, Stata sends a temporary file to a folder to run it. If Stata crashes for some reason, it will loop over those files and copy them to a restore folder. Fair warning, it usually only keeps the last execution of the session, so if you highlighted just a line or two, that's all you'll get back.
*******Set Path Where you want to Copy Files cap program drop syntax_saver program define syntax_saver, rclass syntax [, Restore(string) Today]
if `"`restore'"'=="" local restore_path `"C:/Users/`c(username)'/Desktop/restore/"' if `"`restore'"'!="" local restore_path `"`restore'"' cap mkdir `"`restore'"' cap which ashell if _rc!=0 { ssc install ashell }
tempfile i local gpath `"`i'"' local gp2=regexr(`"`gpath'"',"ST.*","") local gpath=subinstr(`"`gp2'"',"\","/",.) local day=substr(`"`c(current_date)'"',1,2) local day=itrim(trim(`"`day'"')) if `=length(`"`day'"')'==1 local day `"0`day'"' local year=substr(`"`c(current_date)'"',-4,.) if regexm(`"`c(current_date)'"',`"[A-Z][a-z][a-z]"') local lets=regexs(0)
local mct=0 foreach i in `c(Months)' { local ++mct if regexm(`"`i'"',`"`lets'"') local month `"`mct'"' if `=length(`"`month'"')'==1 local month `"0`month'"'
} rmfiles, folder(`"`restore_path'"') match("*") local matchdate `"`month'/`day'/`year'"'
if `"`today'"'!="" di `"Recovering .do files from `matchdate' in temporary folder"' if `"`today'"'=="" di `"Recovering .do files from temporary folder"' cd `"`gp2'"' qui ashell dir /o:-d local restct=0 forvalues i=1/`r(no)' { *di `"`r(o`i')'"' if regexm(`"`r(o`i')'"',"(STD.+tmp)$") { local f=regexs(1) if `"`today'"'!= `""' { if regexm(`"`r(o`i')'"',"^`matchdate'"){ local send=subinstr(`"`restore_path'"',"/","\",.) local take=subinstr(`"`gpath'"',"/","\",.) !robocopy `take' `send' `f' local ++restct } } if `"`today'"'== `""' { local send=subinstr(`"`restore_path'"',"/","\",.) local take=subinstr(`"`gpath'"',"/","\",.) !robocopy `take' `send' `f' local ++restct }
} }
di as smcl `"Restored `restct' files to: {browse `"`restore_path'"'}"'
Returns the file in the specified folder that was most recently saved (and optionally matches some pattern). Sometimes you'll want to save a dataset with a date (say to check for when an error or issue might have been introduced), but then when you want to load it for analysis, you have to change the file name each time, which is annoying. No longer.
cap program drop latest
program define latest, rclass
syntax anything [, Match(string)]
qui {
local cdrestore `"`c(pwd)'"'
cap which ashell
if _rc!=0 {
ssc install ashell
}
local file
local date_time
qui cd `anything'
qui ashell dir /O:D /T:W /A:-D
forvalues i=1/`r(no)' {
if regexm(`"`r(o`i')'"',`"[0-1][0-9]/[0-3][0-9]/20[0-9][0-9]"') {
if `"`match'"'!="" {
if regexm(`"`r(o`i')'"',"`match'") {
tokenize `"`r(o`i')'"'
local file `"`5'"'
local date_time `"`1' `2' `3' "'
}
}
if `"`match'"'=="" {
tokenize `"`r(o`i')'"'
local file `"`5'"'
local date_time `"`1' `2' `3' "'
}
}
}
}
if `"`match'"'=="" di as smcl `"Most recent file is `file' last saved on `date_time'"'
if `"`match'"'!="" di as smcl `"Most recent file matching `"`match'"' is `file' last saved on `date_time'"'
qui cd `cdrestore'
return local file=`"`file'"'
return local date_time=`"`date_time'"'
end
-fixtex- converts characters, which, when meant to be interpreted literally, will cause LaTeX to fail. Do you want to include a $ in your output, or have variable names with underscores? -fixtex- will go through and convert all these characters to their LaTeX-friendly codes.
cap program drop fixtex
program define fixtex, rclass
syntax anything [, Name(string) pull keepcr fixbsl]
local output `anything'
if `"`fixbsl'"'!=`""' local output=subinstr(`"`output'"',"\","\textbackslash{}",.)
foreach i in "&" "_" "#" "%"{
local output=subinstr(`"`output'"',"`i'","\\`i'",.)
}
local output = subinstr(`"`output'"', `">"', `"\textgreater{}"', .) //Greater than sign
local output = subinstr(`"`output'"', `"<"', `"\textless{}"', .) //Less than sign
local output = subinstr(`"`output'"', `"'"', `"\rq{}"', .)
local output = subinstr(`"`output'"', `","', `"`=char(44)'"', .) //comma
local output = subinstr(`"`output'"', `"."', `"`=char(46)'"', .) //period
local output = subinstr(`"`output'"', `"`=char(226)'`=char(128)'`=char(152)'"', `"\lq{}"', .) //Left quote
local output = subinstr(`"`output'"', `"`=char(226)'`=char(128)'`=char(153)'"', `"\rq{}"', .) //Right quote
local output = subinstr(`"`output'"', `"`=char(226)'`=char(128)'`=char(166)'"', `"\ldots{}"', .) //ellipses
local output = subinstr(`"`output'"', `"-"', `"\textminus{}"', .) //Minus or dash
local output = subinstr(`"`output'"', `"`=char(226)'`=char(128)'`=char(147)'"', `"\textendash{}"', .) //En dash
local output = subinstr(`"`output'"', `"`=char(226)'`=char(128)'`=char(148)'"', `"\textemdash{}"', .) //Em Dash
local output=subinstr(`"`output'"',`"\$"',`"\textdollar"',.) //The evil dollar sign
local output=subinstr(`"`output'"',`"`=char(226)'`=char(128)'`=char(156)'"',`"""',.) //Convert directional quotes to non-directional, will add direction later
local output=subinstr(`"`output'"',`"`=char(226)'`=char(128)'`=char(157)'"',`"""',.) //Convert directional quotes to non-directional, will add direction later
local esc=0
*****Pull quotes
while regexm(`"`=char(10)'`output'"',`"""')&`esc'<=12 {
local ++esc
if "`pull'"!="" {
if regexm(`"`output'"',`"`=char(10)'""') {
local output=subinstr(`"`output'"',`"`=char(10)'""',`"\begin{quote} \textquotedblleft \textit{"',1)
if regexm(`"`output'"',`""`=char(10)'"') local output=subinstr(`"`output'"',`""`=char(10)'"',`"}\textquotedblright \end{quote}"',1)
}
}
}
*****Non-Pull quotes
local noutput
local escape=0
local esct=0
while regexm(`"`output'"',`"`=char(34)'"')&`escape'==0&`esct'<12 {
local ++esct
if regexm(`"`output'"',`"`=char(34)'"') { //If non-directional quotes are used, try to given them some direction
local noutput = subinstr(`"`output'"', `"`=char(34)'"', `"\textquotedblleft{}"', 1) //if found(above) convert the first to left in a temp version
if regexm(`"`noutput'"',`"`=char(34)'"') { //If there is a second one
local output = subinstr(`"`noutput'"', `"`=char(34)'"', `"\textquotedblright{}"', 1) //if found(above) convert the second to right
}
if !regexm(`"`noutput'"',`"`=char(34)'"') local escape=1 //If you can't find a second match escape from the while loop
}
}
if `esct'>=7 di as error `"Escaped from Quote: `r(output)'"'
*local output=subinstr(`"`output'"',`"`=char(226)'`=char(128)'`=char(156)'"',`"\textquotedblleft "',.)
*local output=subinstr(`"`output'"',`"`=char(226)'`=char(128)'`=char(157)'"',`"\textquotedblright "',.)
*if `"`keepcr'"'!="" local output = subinstr(`"`output'"', `"`=char(13)'"', `"\par"', .) //New line, CR
if `"`keepcr'"'=="" local output = subinstr(`"`output'"', `"`=char(13)'"', `""', .) //New line, CR
*if `"`keepcr'"'!="" local output = subinstr(`"`output'"', `"`=char(10)'`=char(10)'"', `"\par "', .) //New line, CR
if `"`keepcr'"'=="" local output = subinstr(`"`output'"', `"`=char(10)'`=char(10)'"', `""', .) //New line, CR
local output=subinstr(`"`output'"',`"`=char(226)'"',`""',.)
local output=subinstr(`"`output'"',`"`=char(128)'"',`""',.)
local output=itrim(trim(`"`output'"'))
if `"`name'"'=="" return local output `"`output'"'
if `"`name'"'!="" return local `name' `"`output'"'
end
-latout- is used for checking data across categories, or simple tables and figures for well-organized data (such as survey data). It loops over all of the variables in the dataset (or a list you specify), detects their type and number of distinct values, and then outputs descriptive figures and tables. The -byvars- option allows you to split by years, for example. It is handy for checking data submissions made over multiple years to look for problems. You'll need a LaTeX installation that can be called from the command line for this one.
cap program drop latout program define latout syntax [namelist] [if] [, title(string) /// Title of document, also used to generate name of document PAth(string) /// Where to save output default is current working directory FOOTer(string) /// Text to place at bottom of each page with page number, default Return to top BYVars(varlist max=2) /// variables to split results by up to two Holes(numlist max=1) /// Used to pick where hole is for histograms if odd number of categories of by variable COLumns(numlist max=1) /// Number of columns for by variable histograms CUToff(integer 5) /// Cutoff between categorical and continuous coding Strings(string) ///How to handle strings, list, sample, or skip, default is sample PReserve ///Perserve dataset to make sure no changes to data VARNames] //show variable names in output
local resetcd `"`c(pwd)'"' if `"`namelist'"'!=`""' local varlist `namelist' if `"`namelist'"'==`""' { qui ds * local varlist `"`r(varlist)'"' di `"`varlist'"' } global vlist `"`varlist'"' di `"$vlist"' if `"`preserve'"'!=`""' { tempfile latoutsave sa `latoutsave' }
if `"`if'"'!=`""' { keep `if' } if `"`path'"'==`""' local path `"`=subinstr(`"`c(pwd)'"',"\","/",.)'"' if `"`strings'"'==`""' local strings `"sample"' if inlist(`"`strings'"',`"skip"',`"list"') global skiplist "" /** Saved in ado path cap program drop fixtex program define fixtex, rclass syntax anything [, Name(string)] local output `anything' foreach i in "&" "_" "#" "%"{ local output=subinstr(`"`output'"',"`i'","\\`i'",.) } local output = subinstr(`"`output'"', `">"', `"\textgreater"', .) local output = subinstr(`"`output'"', `"<"', `"\textless"', .) local output = subinstr(`"`output'"', `"'"', `"`=char(39)'"', .) local output = subinstr(`"`output'"', `","', `"`=char(44)'"', .) local output = subinstr(`"`output'"', `"."', `"`=char(46)'"', .) local output=subinstr(`"`output'"',"\$","\\\\$",.) if `"`name'"'=="" return local output `"`output'"' if `"`name'"'!="" return local `name' `"`output'"' end */ ***Set data file here global docname=subinstr(`"`title'"'," ","_",.) global foot "Return to top" //Text at foot of page if `"`footer'"'!="" global foot "`footer'" global udate=subinstr(`"`c(current_date)'"'," ","_",.) global date `c(current_date)' **graphs global ext "pdf" //or pdf or eps if `"${ext}"' != "eps" set graphics on if `"${ext}"' == "pdf" set graphics off //dont need to draw graph if eps set rmsg on
************************************#************************************# ************************************#************************************# ******Settings for command global bylabl global byvar1 global byv global byv "`byvars'" foreach i of global byv { qui count if mi(`i') if `r(N)'>0 { drop if mi(`i') di in red `"Warning!! `r(N)' observations dropped because missing on by variable (`i')"' } } cap drop byvar if `:word count $byv'==2 { local vct=0 foreach v of global byv { local ++vct if regexm(`"`:type `v''"',"str") { gen b`vct'=`v'
} if !regexm(`"`:type `v''"',"str") { if `"`:value label `v''"'!="" { decode `v', gen(b`vct') } if `"`:value label `v''"'=="" { tostring `v', gen(b`vct')
} } label variable b`vct' `"`:variable label `v''"' levelsof b`vct', local(vv`vct') global byvar`vct' "b`vct'" } gen byvar="" foreach v1 of local vv1 { foreach v2 of local vv2 { qui replace byvar="`v1' `v2'" if ${byvar1}=="`v1'"&${byvar2}=="`v2'" } } label variable byvar "`:variable label ${byvar1}'/`:variable label ${byvar2}'" } if `:word count ${byv}'==1 { if regexm(`"`:type ${byv}'"',"str") { gen byvar=$byv } if !regexm(`"`:type ${byv}'"',"str") { if `"`:value label ${byv}'"'!="" { decode $byv, gen(byvar) } if `"`:value label ${byv}'"'=="" { tostring $byv, gen(byvar) } } if `"`byvars'"'=="" global byvar1 if `"`byvars'"'!="" global byvar1 "$byv" if `"`byvars'"'=="" global bylabl if `"`byvars'"'!="" global bylabl `"`: variable label ${byv}'"' } if !inlist(`:word count $byv',0,1,2) { di in red "Can only handle 2 way split" exit }
**********************Checking for existance of all vars global vlist2 "" global nplist "" foreach vl of global vlist { cap confirm variable `vl' if _rc!=0 { di in red `"`vl' not present in dataset"' global nplist "${nplist} `vl'" } if _rc==0 { global vlist2 "${vlist2} `vl'" } } *di `"OLV : ${vlist}"' *di `"NLV : ${vlist2}"' global vlist "${vlist2}" ****DEFINING REPORT COLORS global teal2 "69 156 227" global lblue "9 157 227" //used in latex for headers global mustard "200 174 71" // or "218 152 53" global lyellow "235 194 130" global dkblue "42 90 118" global red2 "173 38 90" global purple2 "114 48 124" global silver "166 169 170"
global ygrid `"ylabel(, grid glwidth(medium) glcolor(gs4) glpattern(dot))"' global xgrid `"xlabel(, grid glwidth(medium) glcolor(gs4) glpattern(dot))"'
global blabel `"blabel(bar, format(%3.0f) color("${teal2}") size(small))"' //For counts where no decimals desired global blabel2 `"blabel(bar, format(%3.0f) color("${teal2}") size(tiny))"' //For counts where no decimals desired, smaller text
set scheme burd4
************************************# ************************************# **2. LATEX DOC cap log close _all cap texdoc stlog close set linesize 89 set rmsg off
*===============WRITE LATEX DOC cap mkdir `"`path'/ltx_output/"' global tex_results `"`path'/ltx_output/"' cap rm `"${tex_results}/${docname}.tex"' cap copy `"C:/ado/personal/stata.sty"' `"${tex_results}stata.sty"' qui cd `"${tex_results}"' texdoc init `"${tex_results}/${docname}.tex"', replace force
qui do `"C:/ado/personal/99_latexpreamble.do"', nostop
tex \fancypagestyle{mypagestyle}{ tex \fancyhf{} tex \fancyfoot[C]{\hyperref[toc]{${foot}} \thepage} %% change [C] to either [L] or [R] if needed. tex \renewcommand{\headrulewidth}{0pt} tex \renewcommand{\footrulewidth}{0pt} tex } tex \usepackage{subfigure} tex \usepackage[subfigure]{tocloft} tex \renewcommand\cftsecfont{\small} tex \renewcommand\cftsecpagefont{\small} tex \renewcommand\cftsubsecfont{\small} tex \renewcommand\cftsubsecpagefont{\small} tex \renewcommand\cftsubsubsecfont{\small} tex \renewcommand\cftsubsubsecpagefont{\small} tex \newcolumntype{Y}{>{\centering\arraybackslash}X} tex \renewcommand*\familydefault{\sfdefault} tex \makeatletter tex \let\ps@plain\ps@mypagestyle%% comment this to keep chapter page style tex %\let\ps@empty\ps@mypagestyle tex \makeatother tex \pagestyle{mypagestyle}
tex \begin{document} tex \pagestyle{plain} tex tex tex \begin{center} tex {\huge{\color{lightblue}{`title'}}} \\[.2cm] tex tex \large{Generated: $date} \\ tex \end{center} tex tex \vspace{1cm} tex tex \tableofcontents\label{toc} tex \noindent\hrulefill tex \addtocontents{toc}{~\hrule\vspace{.2cm}\par} tex \addtocontents{toc}{~\hfill\textbf{Pg.}\par} tex tex tex tex \vspace{.25in} tex tex if `"`holes'"' !="" global hs "holes(`holes')" if `"`columns'"' !="" global cols "col(`columns')" global bhist "" if `"`byvars'"'!="" global bhist `"by(${byvar1} ${byvar2}, ${hs} ${cols})"' global bstatp "" if `:word count ${byv}'==1 global bstatp `"over(${byvar1}, label(labsize(vsmall)))"' if `:word count ${byv}'==2 global bstatp `"over(${byvar2}, label(labsize(vsmall))) over(${byvar1}, label(labsize(vsmall)))"' local ct=0 tex \newpage local totalct `:word count $vlist' global allmilist "" foreach var of global vlist { local typ "" local typ: type `var' qui distinct `var' local encdist=`r(ndistinct)' if regexm(`"`typ'"',"str")&`encdist'<10 { encode `var', gen(`var'2) cap drop temp rename `var' temp rename `var'2 `var' label variable `var' `"`: variable label temp'"' } local ++ct qui count if !mi(`var') if `r(N)'==0 global allmilist "${allmilist} `var'" if `r(N)'>0 { qui distinct `var' local ntosort=`r(ndistinct)' ***IF Continuous***IF Continuous***IF Continuous***IF Continuous***IF Continuous***IF Continuous***IF Continuous***IF Continuous***IF Continuous if `ntosort'>=10&!regexm(`"`: type `var''"',`"str"')&!regexm("`byvars'","`var'") { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \section{`r(output)'} hist `var', ${bhist} xtitle(,size(small)) xsize(5) ysize(6) percent qui graph export `"${tex_results}fig`var'.$ext"', replace as(${ext}) di as smcl `"CLICK TO OPEN: {browse `"${tex_results}fig`var'.$ext"'}"' tex \begin{figure}[ht] tex \centering tex \includegraphics{fig`var'} tex \label{fig:fig`var'} tex \end{figure} tex \begin{center} tex \begin{tabular}{ l c c c c c c c} tex ${bylabl} & Mean & SD & Min & Max & N & Missing & Percent missing \\ \hline if `"`byvars'"'!="" { qui levelsof byvar, local(lvl) foreach l of local lvl { qui count if mi(`var')& byvar=="`l'" local mis `:display %4.0fc `r(N)'' qui sum `var' if byvar=="`l'" if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } qui sum `var' if byvar=="`l'" tex `l' & `:display %7.2f `r(mean)'' & `:display %7.2f `r(sd)'' & `:display %7.2f `r(min)'' & `:display %7.2f `r(max)'' & `:display %4.0fc `r(N)'' & `mis' & `mp' \\ } } if `"`byvars'"'=="" { qui count if mi(`var') local mis `:display %4.0fc `r(N)'' qui sum `var' if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } qui sum `var' tex `l' & `:display %7.2f `r(mean)'' & `:display %7.2f `r(sd)'' & `:display %7.2f `r(min)'' & `:display %7.2f `r(max)'' & `:display %4.0fc `r(N)'' & `mis' & `mp' \\ } tex \end{tabular} tex \end{center}
} ****IF Many category string****IF Many category string****IF Many category string****IF Many category string****IF Many category string****IF Many category string if `ntosort'>=10®exm(`"`: type `var''"',`"str"')&`"`strings'"'==`"sample"' { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \section{`r(output)'} tex `r(output)' is a string variable with more than `cutoff' distinct values. \\ tex \begin{center} tex \begin{tabular}{ l c c c c c c} tex ${bylabl} & N & Distinct &Max length & Min length & Missing & Percent missing \\ \hline qui distinct `var' local en `:display %6.0fc `r(N)'' local dist `:display %6.0fc `r(ndistinct)'' qui count if mi(`var') local mis `:display %4.0fc `r(N)'' qui count if !mi(`var') local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" cap drop lenlatout gen lenlatout=length(`var') qui sum lenlatout loca minl `:display %4.0fc `r(min)'' loca maxl `:display %4.0fc `r(max)'' tex Overall & `en' & `dist' & `maxl' & `minl' & `mis' & `mp' \\ if `"`byvars'"'!="" { qui levelsof byvar, local(lvl) foreach l of local lvl { qui distinct `var' if byvar=="`l'" local en `:display %6.0fc `r(N)'' local dist `:display %6.0fc `r(ndistinct)'' qui count if mi(`var') & byvar=="`l'" local mis `:display %4.0fc `r(N)'' qui count if !mi(`var') & byvar=="`l'" local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" qui sum lenlatout if byvar=="`l'" loca minl `:display %4.0fc `r(min)'' loca maxl `:display %4.0fc `r(max)'' tex `l' & `en' & `dist' & `maxl' & `minl' & `mis' & `mp' \\ } drop lenlatout } tex \end{tabular} tex \end{center} **************************************** tex \begin{center} tex \begin{tabularx}{\linewidth}{@{}|Y |Y| Y| Y|@{}} if `"`byvars'"'!=""|`"`byvars'"'=="" { tex \hline tex \multicolumn{4}{|c|}{Sample Values} \\ \hline qui count local mxn=`r(N)' forvalues i=1/10 { local tclin forvalues j=1/4 { local wct=0 while `"`holder'"'==`""'|`wct'<15 { local ++wct local test=trunc(runiform()*`mxn') if `test'==0 local test "1" if `test'>`=_N' local test "`=_N'" local holder `"`=`var'[`test']'"' } fixtex `"`holder'"', n(inp) if `j'==1 local tclin "`r(inp)'" if `j'>1 local tclin "`tclin' & `r(inp)'" } tex `tclin' \\ \hline } tex \end{tabularx} tex \end{center} }
**************************************** } if `ntosort'>10®exm(`"`: type `var''"',`"str"')&inlist(`"`strings'"',`"skip"',`"list"') { global skiplist "$skiplist `var'" }
*****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories if `ntosort'>2&`ntosort'<=`cutoff'&!regexm("`byvars'","`var'") { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' ==`""' tex \section{`r(output)'} if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} cap drop vtsp? qui ta `var', g(vtsp) foreach vtsp of varlist vtsp? { qui replace `vtsp'=`vtsp'*100 local bulls =subinstr(`"`:variable label `vtsp''"',"`var'==","",.) label variable `vtsp' `"`bulls'"' } statplot vtsp?, $bstatp xpose s(mean) xsize(5) ysize(5) ytitle(Percent of responses) varopts(label(labsize(vsmall))) cap drop vtsp? qui graph export `"${tex_results}fig`var'.$ext"', replace as(${ext}) di as smcl `"CLICK TO OPEN: {browse `"${tex_results}fig`var'.$ext"'}"' tex \begin{figure}[ht] tex \centering tex \includegraphics{fig`var'} tex \label{fig:fig`var'} tex \end{figure} if `"`byvars'"'!="" { tex \begin{center} global texhed " l" global texline "${bylabl}" qui levelsof `var', local(tlevs) foreach tl of local tlevs { global texhed "${texhed} Y" fixtex `"`: label (`var') `tl''"', n(llbl) global texline `"$texline & `r(llbl)'"' } local shrink=0 local len =length(`"$texline"') if `len'>90 local shrink=1 if `"`shrink'"'=="1" tex \begin{footnotesize} global texhed `"${texhed} Y Y Y "' global texline `"$texline & N & Missing & \% Missing "' tex \begin{tabularx}{\linewidth}{@{}${texhed}@{}} tex ${texline} \\ \hline qui levelsof byvar, local(lvl) foreach l of local lvl { global texline "`l'" qui count if byvar=="`l'"&!mi(`var') local totl=`r(N)' qui levelsof `var', local(tlevs) foreach tl of local tlevs { qui count if byvar=="`l'"&`var'==`tl' if `totl'>0 local perc: display %5.2f `=`r(N)'/`totl'*100' if `totl'==0 local perc "MI" if `totl'>0 global texline "$texline & `perc'\%" if `totl'==0 global texline "$texline & `perc'" } qui count if mi(`var')& byvar=="`l'" local mis `:display %4.0fc `r(N)'' qui sum `var' if byvar=="`l'" if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } global texline "$texline & `:display %4.0fc `totl'' & `mis' & `mp'"
tex $texline \\ } tex \end{tabularx} if `"`shrink'"'=="1" tex \end{footnotesize}
} if `"`byvars'"'=="" { tex \begin{center} global texhed " l c c " qui count if !mi(`var') local tbtot=`r(N)' local shrink=0 if `"`shrink'"'=="1" tex \begin{footnotesize} tex \begin{tabular}{${texhed}} qui levelsof `var', local(tlevs) tex `: variable label `var'' & N & Percent \\ \hline foreach tl of local tlevs { qui count if `var'==`tl' local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/`tbtot'*100'' global texline "`: label (`var') `tl'' & `tbn' & `tbperc'\%" tex ${texline} \\
} qui count if !mi(`var') tex Total non-missing & `r(N)' & 100.00 \% \\ \hline qui count if mi(`var') local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/(`tbtot'+`r(N)')*100'' tex Missing & `tbn' & `tbperc' \% \\ \hline tex \end{tabular} } tex tex tex \end{center} } //End of fewer than 6 values and not a by variable loop *****IF Categorical and more then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories *****IF Categorical and less then 5 categories if `ntosort'>`cutoff'&`ntosort'<10&!regexm("`byvars'","`var'") { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \section{`r(output)'} cap drop vtsp? qui ta `var', g(vtsp) global leglab "" local lgct=0 foreach vtsp of varlist vtsp? { local ++lgct qui replace `vtsp'=`vtsp'*100 local bulls =subinstr(`"`:variable label `vtsp''"',"`var'==","",.) label variable `vtsp' `"`bulls'"' global leglab `"$leglab label(`lgct' "`:variable label `vtsp''")"' } graph hbar vtsp?, by(byvar) legend(${leglab}) xsize(5) ysize(6) cap drop vtsp? qui graph export `"${tex_results}fig`var'.$ext"', replace as(${ext}) di as smcl `"CLICK TO OPEN: {browse `"${tex_results}fig`var'.$ext"'}"' tex \begin{figure}[ht] tex \centering tex \includegraphics{fig`var'} tex \label{fig:fig`var'} tex \end{figure} if `"`byvars'"'!="" { tex \begin{center} global texhed " l" global texline "${bylabl}" qui levelsof `var', local(tlevs) foreach tl of local tlevs { global texhed "${texhed} Y" fixtex `"`: label (`var') `tl''"', n(llbl) global texline `"$texline & `r(llbl)'"' } local shrink=0 local len =length(`"$texline"') if `len'>90 local shrink=1 if `"`shrink'"'=="1" tex \begin{footnotesize} global texhed `"${texhed} Y Y Y "' global texline `"$texline & N & Missing & \% Missing "' tex \begin{tabularx}{\linewidth}{@{}${texhed}@{}} tex ${texline} \\ \hline qui levelsof byvar, local(lvl) foreach l of local lvl { global texline "`l'" qui count if byvar=="`l'"&!mi(`var') local totl=`r(N)' qui levelsof `var', local(tlevs) foreach tl of local tlevs { qui count if byvar=="`l'"&`var'==`tl' if `totl'>0 local perc: display %5.2f `=`r(N)'/`totl'*100' if `totl'==0 local perc "MI" if `totl'>0 global texline "$texline & `perc'\%" if `totl'==0 global texline "$texline & `perc'" } qui count if mi(`var')& byvar=="`l'" local mis `:display %4.0fc `r(N)'' qui sum `var' if byvar=="`l'" if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } global texline "$texline & `:display %4.0fc `totl'' & `mis' & `mp'"
tex $texline \\ } tex \end{tabularx} if `"`shrink'"'=="1" tex \end{footnotesize} } if `"`byvars'"'=="" { tex \begin{center} global texhed " l c c " qui count if !mi(`var') local tbtot=`r(N)' local shrink=0 if `"`shrink'"'=="1" tex \begin{footnotesize} tex \begin{tabular}{${texhed}} qui levelsof `var', local(tlevs) tex `: variable label `var'' & N & Percent \\ \hline foreach tl of local tlevs { qui count if `var'==`tl' local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/`tbtot'*100'' global texline "`: label (`var') `tl'' & `tbn' & `tbperc'\%" tex ${texline} \\
} qui count if !mi(`var') tex Total non-missing & `r(N)' & 100.00 \% \\ \hline qui count if mi(`var') local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/(`tbtot'+`r(N)')*100'' tex Missing & `tbn' & `tbperc' \% \\ \hline tex \end{tabular} } tex tex tex \end{center} } //End of more than 6 values but fewer than 10 and not a by variable loop
*****IF Dichotomous *****IF Dichotomous *****IF Dichotomous *****IF Dichotomous *****IF Dichotomous *****IF Dichotomous if `ntosort'==2&!regexm("`byvars'","`var'") { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \section{`r(output)'} cap drop vtsp? qui ta `var', g(vtsp) foreach vtsp of varlist vtsp? { qui replace `vtsp'=`vtsp'*100 local bulls =subinstr(`"`:variable label `vtsp''"',"`var'==","",.) label variable `vtsp' `"`bulls'"' } statplot vtsp?, $bstatp s(mean) xsize(5) ysize(5) ytitle(Percent of responses) varopts(label(labsize(vsmall))) cap drop vtsp? qui graph export `"${tex_results}fig`var'.$ext"', replace as(${ext}) di as smcl `"CLICK TO OPEN: {browse `"${tex_results}fig`var'.$ext"'}"' tex \begin{figure}[ht] tex \centering tex \includegraphics{fig`var'} tex \label{fig:fig`var'} tex \end{figure}
if `"`byvars'"'!="" { tex \begin{center} global texhed " l" global texline "${bylabl}" qui levelsof `var', local(tlevs) foreach tl of local tlevs { global texhed "${texhed} Y" fixtex `"`: label (`var') `tl''"', n(llbl) global texline `"$texline & `r(llbl)'"' } local shrink=0 local len =length(`"$texline"') if `len'>90 local shrink=1 if `"`shrink'"'=="1" tex \begin{footnotesize} global texhed `"${texhed} Y Y Y "' global texline `"$texline & N & Missing & \% Missing "' tex \begin{tabularx}{\linewidth}{@{}${texhed}@{}} tex ${texline} \\ \hline qui levelsof byvar, local(lvl) foreach l of local lvl { global texline "`l'" qui count if byvar=="`l'"&!mi(`var') local totl=`r(N)' qui levelsof `var', local(tlevs) foreach tl of local tlevs { qui count if byvar=="`l'"&`var'==`tl' if `totl'>0 local perc: display %5.2f `=`r(N)'/`totl'*100' if `totl'==0 local perc "MI" if `totl'>0 global texline "$texline & `perc'\%" if `totl'==0 global texline "$texline & `perc'" } qui count if mi(`var')& byvar=="`l'" local mis `:display %4.0fc `r(N)'' qui sum `var' if byvar=="`l'" if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } global texline "$texline & `:display %4.0fc `totl'' & `mis' & `mp'"
tex $texline \\ } tex \end{tabularx} if `"`shrink'"'=="1" tex \end{footnotesize}
} if `"`byvars'"'=="" { tex \begin{center} global texhed " l c c " qui count if !mi(`var') local tbtot=`r(N)' local shrink=0 if `"`shrink'"'=="1" tex \begin{footnotesize} tex \begin{tabular}{${texhed}} qui levelsof `var', local(tlevs) tex `: variable label `var'' & N & Percent \\ \hline foreach tl of local tlevs { qui count if `var'==`tl' local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/`tbtot'*100'' global texline "`: label (`var') `tl'' & `tbn' & `tbperc'\%" tex ${texline} \\
} qui count if !mi(`var') tex Total non-missing & `r(N)' & 100.00 \% \\ \hline qui count if mi(`var') local tbn `:display %8.0fc `r(N)'' local tbperc `:display %8.2fc `=`r(N)'/(`tbtot'+`r(N)')*100'' tex Missing & `tbn' & `tbperc' \% \\ \hline tex \end{tabular} } tex tex tex \end{center} } //End of dichotomous *****By variables*****By variables*****By variables*****By variables*****By variables*****By variables*****By variables*****By variables*****By variables if regexm("`byvars'","`var'") { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \section{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \section{`r(output)'} cap drop vtsp? qui ta `var', g(vtsp) foreach vtsp of varlist vtsp? { qui replace `vtsp'=`vtsp'*100 local bulls =subinstr(`"`:variable label `vtsp''"',"`var'==","",.) label variable `vtsp' `"`bulls'"' } statplot vtsp?, s(mean) xsize(5) ysize(5) ytitle(Percent of responses) varopts(label(labsize(vsmall))) cap drop vtsp? qui graph export `"${tex_results}fig`var'.$ext"', replace as(${ext}) di as smcl `"CLICK TO OPEN: {browse `"${tex_results}fig`var'.$ext"'}"' tex \begin{figure}[ht] tex \centering tex \includegraphics{fig`var'} tex \label{fig:fig`var'} tex \end{figure} tex \begin{center} global texhed " l" global texline "${bylabl}" qui levelsof `var', local(tlevs) foreach tl of local tlevs { global texhed "${texhed} Y" fixtex `"`: label (`var') `tl''"', n(llbl) global texline `"$texline & `r(llbl)'"' } local shrink=0 local len =length(`"$texline"') if `len'>90 local shrink=1 if `"`shrink'"'=="1" tex \begin{footnotesize} global texhed `"${texhed} Y Y Y "' global texline `"$texline & N & Missing & \% Missing "' tex \begin{tabularx}{\linewidth}{@{}${texhed}@{}} tex ${texline} \\ \hline global texline "`divar'" qui count if !mi(`var') local totl=`r(N)' qui levelsof `var', local(tlevs) foreach tl of local tlevs { qui count if `var'==`tl' if `totl'>0 local perc: display %5.2f `=`r(N)'/`totl'*100' if `totl'==0 local perc "MI" if `totl'>0 global texline "$texline & `perc'\%" if `totl'==0 global texline "$texline & `perc'" } qui count if mi(`var') local mis `:display %4.0fc `r(N)'' qui sum `var' if `mis'>0 { local mp "`:display %5.2f `=`mis'/(`r(N)'+`mis')*100''\%" } if `mis'>0&`r(N)'==0 { local mp "100\%" } if `mis'==0 { local mp "0.00\%" } global texline "$texline & `:display %4.0fc `totl'' & `mis' & `mp'" tex $texline \\ tex \end{tabularx} if `"`shrink'"'=="1" tex \end{footnotesize} tex tex tex \end{center} } //End of by variable loop } //End of if not zero loop
tex \newpage di as smcl `"`var' `ct' of `totalct' complete for `title' "' if regexm(`"`typ'"',"str")&`encdist'<10 { drop `var' rename temp `var' }
} //End of variables loop *******If many cateogry string and list selected *******If many cateogry string and list selected *******If many cateogry string and list selected if `"$skiplist"'!=""&`"`strings'"'==`"list"' { tex \section{Open Ended Responses} foreach var of global skiplist { fixtex `"`: variable label `var''"' local divar=subinstr(`"`var'"',"_","\_",.) if `"`varnames'"' !=`""' tex \subsection{`r(output)' (`divar')} if `"`varnames'"' ==`""' tex \subsection{`r(output)'} qui replace `var'=trim(`var') tex \vspace{.15cm} if `"`byvars'"'==`""' { qui count local oect=0 forvalues obs=1/`r(N)' { if `"`=`var'[`obs']'"'!=`""' { local ++oect tex tex \vspace{.05cm} tex Text from observation `obs': \\ fixtex `"`=`var'[`obs']'"' tex `r(output)' \\ tex \vspace{.05cm} tex \hrule } } } if `"`byvars'"'!=`""' { qui count local totn `r(N)' local oect=0 qui levelsof byvar, local(bylev) foreach bl of local bylev { tex \subsubsection{`bl'} tex \vspace{1cm} tex \hrule forvalues obs=1/`totn' { if `"`=`var'[`obs']'"'!=`""'&`"`=byvar[`obs']'"'==`"`bl'"' { local ++oect tex tex \vspace{.05cm} fixtex `"`=`var'[`obs']'"' tex `r(output)' \\ tex \vspace{.05cm} tex \hrule } } tex \newpage } } } di in red `"`oect' Open-ended responses output for `divar'"' } if `"$skiplist"'!=""&`"`strings'"'==`"skip"' { tex \section{Skipped String Variables} tex The following variables are string variables with many categories and were skipped: \\ tex \begin{itemize} foreach mi of global skiplist { local lab: variable label `mi' fixtex `"`lab' (`mi')"' tex \item `r(output)' } tex \end{itemize}
} if "${allmilist}"!=""|"${nplist}"!="" { tex \section{Missing Variables} if "${allmilist}"!="" { tex The following variables are missing for all observations: \\ tex \begin{itemize} foreach mi of global allmilist { local lab: variable label `mi' fixtex `"`lab' (`mi')"' tex \item `r(output)' } tex \end{itemize} } if "${nplist}"!="" { tex The following variables are not present in the dataset: \\ tex \begin{itemize} foreach mi of global nplist { local mi2=subinstr(`"`mi'"',"_","\_",.) tex \item `mi2' } tex \end{itemize} }
} *=========================END DOCUMENT============================* tex \end{document} texdoc close *=================================================================*
*---------------------Typeset LaTeX Document----------------------* local gg "${docname}" foreach fff in `gg' { **first** if "`c(os)'" == "Windows"{ cap rm "${tex_results}/`fff'.pdf" !taskkill /f /im "Acrobat.exe" /t !pdflatex "${tex_results}/`fff'.tex" **need bibtex command** !pdflatex "${tex_results}/`fff'.tex" di as smcl `"Click to open directory: {browse `"${tex_results}"'}"' di as smcl `"CLICK TO OPEN: {browse `"${tex_results}/`fff'.pdf"'}"' ****!"C:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe" "${sf}/`fff'.pdf" } //end windows loop
if "`c(os)'" == "MacOSX" { cap rm "${tex_results}/`fff'.pdf" **test: !/usr/texbin/bibtex "${sf}/aisdreport_year1" !/Library/TeX/texbin/pdflatex --shell-escape "${tex_results}/`fff'.tex" "${tex_results}/`fff'" !/Library/TeX/texbin/bibtex "${latex}/`f'" !/Library/TeX/texbin/pdflatex --shell-escape "${tex_results}/`fff'.tex" "${tex_results}/`fff'" **!/usr/texbin/makeindex --shell-escape "${latex}/`fff'.idx" "${latex}/`fff'" !/Library/TeX/texbin/pdflatex --shell-escape "${tex_results}/`fff'.tex" "${tex_results}/`fff'" *copy pdf file to subdirectory cap copy "${tex_results}/`fff'.pdf" "${tex_results}/`fff'.pdf", /// replace public !open "${results_tex}/`fff'.pdf" di as smcl `"Click to open directory: {browse `"${tex_results}"'}"' di as smcl `"CLICK TO OPEN: {browse `"${tex_results}/`fff'.pdf"'}"'
*clean up adjunct files [[b/c not updating new pdf files properly]] foreach clean in pdf log aux tex ind idx { * cap rm "${latex}/`fff'.`clean'" }
} //end mac loop copy `"`path'/ltx_output/`fff'.pdf"' `"`path'/`fff'.pdf"', replace } //end fff loop for compiling if `"`preserve'"'!=`""' { use `latoutsave', clear }
-texpdf- processes .tex files. This way, you don't have to compile it separately.
cap program drop texpdf
program define texpdf, rclass
syntax using/ [, Copy(string) open]
local oldcd `"`c(pwd)'"'
****parsing out file name and path
local toparse=subinstr(`"`using'"',`"\"',`"/"',.)
if regexm(`"`toparse'"',"(.+/)(.+\.tex$)") {
local path=regexs(1)
local file=regexs(2)
}
local copy2=subinstr(`"`copy'"',`"\"',`"/"',.)
***Confirming has version of stata.sty in personal folder, if not get from S
cap confirm file `"`c(sysdir_personal)'stata.sty"'
if _rc!=0 {
if "`c(os)'" == "Windows" copy `"S:/data/draft_ado_files/gibson_ados/stata.sty"' `"`c(sysdir_personal)'stata.sty"'
if "`c(os)'" == "MacOSX" copy `"/Volumes/shared/data//draft_ado_files/gibson_ados/stata.sty"' `"`c(sysdir_personal)'stata.sty"'
}
***Confirming has version of stata.sty in folder with .tex file, if not get from personal
cap confirm file `"`path'stata.sty"'
if _rc!=0 {
copy `"`c(sysdir_personal)'stata.sty"' `"`path'stata.sty"'
}
cd `"`path'"'
cd
*---------------------Typeset LaTeX Document----------------------*
local fff=subinstr(`"`file'"',".tex","",1)
**first**
if "`c(os)'" == "Windows"{
!taskkill /f /im "cmd.exe" /t
!taskkill /f /im "Acrobat.exe" /t
!taskkill /f /im "AcroRd32.exe" /t
cap rm `"`path'/`fff'.pdf"'
!pdflatex "`path'/`fff'.tex"
**need bibtex command**
!pdflatex "`path'/`fff'.tex"
di as smcl `"Click to open directory: {browse `"`path'"'}"'
di as smcl `"CLICK TO OPEN: {browse `"`path'/`fff'.pdf"'}"'
sleep 1000
if `"`copy2'"'!=`""' cap copy `"`path'/`fff'.pdf"' `"`copy2'/`fff'.pdf"', replace
if `"`open'"'==`"open"' shell start "AcroRd32.exe" "`path'/`fff'.pdf"
} //end windows loop
if "`c(os)'" == "MacOSX" {
cap rm "`path'/`fff'.pdf"
**test: !/usr/texbin/bibtex "${sf}/aisdreport_year1"
!/Library/TeX/texbin/pdflatex --shell-escape "`path'/`fff'.tex" "`path'/`fff'"
!/Library/TeX/texbin/bibtex "`path'/`fff'"
!/Library/TeX/texbin/pdflatex --shell-escape "`path'/`fff'.tex" "`path'/`fff'"
**!/usr/texbin/makeindex --shell-escape "${latex}/`fff'.idx" "${latex}/`fff'"
!/Library/TeX/texbin/pdflatex --shell-escape "`path'/`fff'.tex" "`path'/`fff'"
*copy pdf file to subdirectory
if `"`copy2'"'!=`""' cap copy `"`path'/`fff'.pdf"' `"`copy2'`fff'.pdf"', replace
replace public
!open "`path'/`fff'.pdf"
di as smcl `"Click to open directory: {browse `"`path'"'}"'
di as smcl `"CLICK TO OPEN: {browse `"`path'/`fff'.pdf"'}"'
-conwithin- checks for whether variable list a is constant within variable list b, regardless of type. If you've ever worked with panel or nested data where some variable should be constant within some other, you can use this to check it.
cap program drop conwithin
program def conwithin, rclass
syntax [ varlist ] [if] [, WIthin(string) Tag(string)]
tempvar sortorder
gen `sortorder'=_n
local notcon=0
local conlist
local notconlist
tokenize `within'
while `"`*'"'!="" {
if `"`if'"'!=`""' qui count `if' & mi(`1')
if `"`if'"'==`""' qui count if mi(`1')
if `r(N)'>0 di as error `"Warning `: display %10.0gc `r(N)'' observations missing on `1' excluded"'
macro shift
}
*****Cofirming existance of by variables
foreach wvar in `within' {
cap confirm variable `wvar'
if _rc!=0 {
di as error `"`wvar' (within variable) not found"'
exit
}
}
foreach var in `varlist' {
cap confirm variable `var'
if _rc!=0 {
di as error `"`var' (varlist variable) not found"'
exit
}
if regexm(`"`:type `var''"',"str") {
tempvar conwtt
cap drop `conwtt'
qui encode `var', gen(`conwtt')
}
if !regexm(`"`:type `var''"',"str") {
tempvar conwtt
cap drop `conwtt'
qui gen `conwtt'=`var'
}
tempvar contst
cap drop `contst'
qui bysort `within': egen `contst'=sd(`conwtt')
tempvar conuse
qui gen `conuse'=1 `if'
tokenize `within'
while `"`*'"'!="" {
qui replace `conuse'=. if mi(`1')
macro shift
}
qui replace `contst'=. if `conuse'!=1
qui replace `contst'=1 if `contst'>0&!mi(`contst')
qui sum `contst'
local ENN=`r(N)'
if `ENN'==0 {
di as result `"Only 1 non-missing observation of `var' per `within'"'
local conlist `"`conlist' `var'"'
}
if `ENN'!=0 {
local MEEN=`r(mean)'
if `MEEN'==0 {
di as result `"`var' constant within `within'"'
local conlist `"`conlist' `var'"'
}
if `MEEN'!=0 {
qui count if `conuse'==1
local contot=`r(N)'
qui count if `conuse'==1&`contst'==1
local connum=`r(N)'
di as error `"`var' NOT constant within `within'"'
di as error `" `connum' of `contot' Records (`:display %4.1f `=`connum'/`contot'*100''%) "'
local notconlist `"`notconlist' `var'"'
local ++notcon
if `"`tag'"'!=`""' {
cap confirm variable `var'_`tag'
if _rc==0 {
di as error `"`var'_`tag' already defined"'
di as result `"No tag generated"'
}
cap confirm variable `var'_`tag'
if _rc!=0 {
qui gen `var'_`tag'=1 if `contst'==1
di as result `"Observations not consistent tagged as `var'_`tag'"'
}
}
}
}
}
return scalar notcon=`notcon'
return local notconlist=`"`notconlist'"'
return local conlist=`"`conlist'"'
sort `sortorder'
end
Here is the code I used to write this website. 00_control.do defines some characteristics that are propagated to each page. It also determines which pages are run and the order they are presented in the sidebar menu. 01_css establishes the style, and the rest write each of the other pages. I have only attached these for download because I think including the code was a bit too meta for webdoc to handle. Embedding Stata code displayed in HTML written from Stata using Stata .ados was more difficult than one might initially think. When code written by that Stata file is also HTML, the problem compounds.
**! new geometry for 1.5 col page. tex \usepackage[left=0in, right=0in,top=0in,bottom=0in]{geometry} **tex \usepackage[left=1.25in,right=1in,top=1in,bottom=1.1in]{geometry} global halfpage `"\newgeometry{includemp,left=1.25in,right=2in,top=1in,bottom=1.1in,marginparsep = 10mm,marginparwidth = 1.5in}"' ** use \restoregeometry% to restore after global halfpage shift in geometry **includemp builds the margin space for the margin figure - you need it!
************* tex %pstricks invoked by stata needs this to run pdflatex but this breaks pdfpages!!!!!! tex %\usepackage{auto-pst-pdf} tex \usepackage[final]{pdfpages} *************
tex tex %myqed -- same as above but doesnt move to end of line like halmos% tex %\newcommand{\myqed}{\rule{5pt}{5pt}} tex \newcommand{\myqed}{\setlength\fboxrule{.5pt}\setlength\fboxsep{.5pt}\fbox{\phantom{\rule{3pt}{3pt}}}} tex %also mine is smaller and thinner, more obscure tex **new colored symbols for maps
tex %%dashed table line option tex \usepackage{arydshln} tex %set up arydshln environment tex \setlength{\dashlinedash}{0.4pt} tex \setlength{\dashlinegap}{1pt} tex \setlength{\arrayrulewidth}{.5pt}
** \hdashline
tex tex %%fix chapter/section style via titlesec tex \usepackage{titlesec} tex tex %\titleformat{\section}[hang]{\Huge\bfseries}{\thesection\textcolor{blue}{|}}{0pt}{\Huge\bfseries} tex tex tex tex % chapter format tex \titleformat{\chapter}% tex {\huge\rmfamily\upshape\color{lightblue}}% format applied to label+text tex {\llap{\colorbox{lightblue}{\parbox{1.5cm}{\hfill\upshape\huge\color{white}\thechapter}}}}% label tex {2pt}% horizontal separation between label and title body tex {}% before the title body tex []% after the title body tex tex % section format tex \titleformat{\section}% tex {\normalfont\huge\upshape\color{darkgrey}}% format applied to label+text tex {\llap{\colorbox{lightblue}{\parbox{1.5cm}{\hfill\color{white}\thesection}}}}% label tex {1em}% horizontal separation between label and title body tex {}% before the title body tex []% after the title body tex tex %%%subsection format tex %%%%%UNCOMMENT TO USE tex \titleformat{\subsection}% tex {\normalfont\Large\bfseries\color{darkgrey}}% format applied to label+text tex {\llap{\colorbox{lightblue}{\parbox{1.5cm}{\hfill\color{white}\thesubsection}}}}% label tex {1em}% horizontal separation between label and title body tex {}% before the title body tex []% after the title body tex tex tex \titleformat{\subsubsection}% tex {\normalfont\large\itshape\color{lightblue}}% format applied to label+text tex {\llap{\colorbox{lightblue}{\parbox{1.5cm}{\hfill\color{white}\thesubsubsection}}}}% label tex {1em}% horizontal separation between label and title body tex {}% before the title body tex []% after the title body tex tex %%%%TIMELINE macro tex \newcommand\ytl[2]{ tex \parbox[b]{8em}{\hfill{\color{lightblue}\bfseries\sffamily #1}~$\cdots\cdots$~}\makebox[0pt][c]{$\bullet$}\vrule\quad \parbox[c]{4.5cm}{\vspace{7pt}\color{darkblue}\raggedright\sffamily #2.\\[7pt]}\\[-3pt]} tex tex tex \titleformat{\paragraph} tex {\normalfont\normalsize\bfseries}{\theparagraph}{1em}{} tex \titlespacing*{\paragraph} tex {0pt}{3.25ex plus 1ex minus .2ex}{1.5ex plus .2ex} tex tex \setcounter{secnumdepth}{4} %allows for paragraph tex \setcounter{tocdepth}{3} %%toc level tex %\renewcommand\abstractname{ } %%gets rid of abstract title, could change it instead tex
tex %%tcolorboxes for footerpage # and for definitions
clear all
set more off
set trace off
set linesize 105
if c(os)=="Windows" global sf "S:/Users/DanialHoepfner/resume/"
global raw "${sf}/raw//"
global latex "${sf}/latex/"
global syntax "${sf}/syntax/"
foreach f in raw latex syntax {
cap mkdir `"${`f'}"'
}
global pages `"c:/personal/website/pages/"'
set scheme plotplainblind
local fontt `"Georgia"' //"Futura Bk BT" Arial Georgia helvetica
graph set window fontface `"`fontt'"'
graph set window fontfacesans `"`fontt'"'
graph set ps fontface `"`fontt'"'
****DEFINING REPORT COLORS
*global teal2 "69 156 227" //original
global teal2 "30 166 225"
global lblue "9 157 227" //used in latex for headers
global mustard "200 174 71" // or "218 152 53"
global lyellow "235 194 130"
global dkblue "42 90 118"
global red2 "173 38 90"
global orange "252 132 20"
global purple2 "114 48 124"
global silver "166 169 170"
global giblue "20 065 92"
global myorange "252 132 20"
********************************Set colors here!********************************
********************************Set colors here!********************************
********************************Set colors here!********************************
local bodytext `"black"'
local sidetext `"black"'
local bodyhead `"giblue2"'
local sidehead `"giblue2"'
local sidefill `"dirtywhite2"' //statablack
local bodyfill `"dirtywhite2"'
local border `"gray"'
local borderfill `"statablack"'
local highcolor `"commentgreen"'
******************************End Set colors here!******************************
******************************End Set colors here!******************************
******************************End Set colors here!******************************
******************************Getting resume urls*******************************
******************************Getting resume urls*******************************
di `"${syntax}"'
global resadonamelist
import excel `"${syntax}adolist_res.xlsx"', clear first
forvalues i=1/`=_N-1' {
global `=ado[`i']'p `"`=path[`i']'"'
global resadonamelist ${resadonamelist} `=ado[`i']'
}
macro list
clear
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
*********************************************************LATEX DOC*****************************************************#
**2. LATEX DOC
cap log close _all
cap texdoc stlog close
set linesize 89
set rmsg off
cd `"${latex}"'
****testing conditions
cap texdoc close
local docname `"danial_hoepfner_resume"'
di `"`docname'"'
texdoc init `"${latex}`docname'.tex"', replace force
**********************************Skill boxes***********************************
**********************************Skill boxes***********************************
**********************************Skill boxes***********************************
local stata \blacksquare{}\blacksquare{}\blacksquare{}\blacksquare{}\blacksquare{}
local python \blacksquare{}\blacksquare{}\square{}\square{}\square{}
local latex \blacksquare{}\blacksquare{}\blacksquare{}\blacksquare{}\square{}
local html \blacksquare{}\blacksquare{}\blacksquare{}\square{}\square{}
local api \blacksquare{}\blacksquare{}\blacksquare{}\square{}\square{}
local R \blacksquare{}\square{}\square{}\square{}\square{}
********************************End Skill boxes*********************************
********************************End Skill boxes*********************************
********************************End Skill boxes*********************************
*!DH Note 3/1/2023: Remove phone number and address for online version
local for_web 1
local siderows 2 //set number of main text sections here, history , education, highlights etc.
************************************Sidebar*************************************
************************************Sidebar*************************************
************************************Sidebar*************************************
edrop date
set obs 1
gen date=date(`"`c(current_date)'"',`"DMY"')
format date %td
gen gibstartdate=date(`"31 May 2016"',`"DMY"')
gen gibyears=(date-gibstartdate)/365
gen pittstartdate=date(`"31 Aug 2010"',`"DMY"')
fre date
gen pittyears=(gibstartdate-pittstartdate)/365
local ldate `"`:di %tdMonth_CCYY `=gibstartdate[1]''"'
local ldate `"`=trim(`"`ldate'"')'"'
tex {\textcolor{`bodyhead'}{\vspace{-.05cm} \newline {\large\textbf{Highlights}}} \newline}
tex \begin{singlespace}
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex {\vspace{.1cm}\footnotesize{}{\textcolor{`highcolor'}{\textbf{Quantitative Data Analysis:}}}}{\scriptsize{}\textcolor{`bodytext'}~`:di %2.1f `=gibyears[1]'' years of professional experience conducting sophisticated data analysis for program evaluation and survey projects. Known for finding creative solutions to analytic and programming problems. Experience with various regression and quasi-experimental designs, including multi-level models, structural equation models, Mahalanobis and propensity score matching. Create compelling and informative \href{http://www.danialhoepfner.com/visualization.html}{visualizations} and report-ready tables.} \vspace{.2cm} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Data Management:}}}}{\scriptsize{}\textcolor{`bodytext'}~Extensive experience in creating analytic datasets from a variety of sources, including surveys, school district and college administrative data, statewide student data, and publically available data. Adept at checking, cleaning, merging, and appending messy, incomplete, and poorly structured data for analysis. Able to use APIs and web scraping to gather and incorporate publically available data.} \vspace{.2cm} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Productivity:}}}}{\scriptsize{}\textcolor{`bodytext'}~Able to automate tasks in all aspects of research, including data gathering and processing; output for checking data integrity; subunit PDF reports; survey response rates and resources web portals; web scraping; API interaction. Skilled at writing \href{http://www.danialhoepfner.com/code.html}{extensible code and programs} to speed data management and reporting tasks. Typically work on multiple projects with competing deadlines in a fast-paced environment.} \vspace{.2cm} \\ \hline
tex \end{tabular}
tex \vspace{.2cm}
tex \end{singlespace}
*********************************End Highlights*********************************
*********************************End Highlights*********************************
*********************************End Highlights*********************************
tex \vspace{-.1cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Gibson Consulting~~~~~~~~~~~~~~~~~~~~~~~Director in Research and Evaluation Group~~~~~~~~~~~~~~~~~`ldate' to Now}}}} \vspace{-.15cm} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\scriptsize{}Director for an Austin-based education research and consulting firm on program evaluation and survey projects. Manage projects, provide training and support for junior quantitative staff, participate in group and firm decision-making. Conduct innovative analysis using a wide variety of methods. Create impressive \href{http://www.danialhoepfner.com/visualization.html}{visualizations} and \href{http://www.danialhoepfner.com/visualization.html\#pdfs}{automated summary reports}. Streamline and automate most aspects of data collection, preparation, analysis and reporting.} \\ \hline
tex \end{tabular}
tex \end{singlespace}
*!DH Note 5/11/2020: Pitt
local ldate `"`:di %tdMonth_CCYY `=pittstartdate[1]''"'
local ldate `"`=trim(`"`ldate'"')'"'
tex \vspace{.3cm} {\textcolor{`bodytext'}{\footnotesize{\textbf{University of Pittsburgh~~~~~~~~~~~~~~~~~~~~~~~~Research and Teaching Fellow~~~~~~~~~~~~~~ `ldate' to `:di %tdMonth_CCYY `=gibstartdate[1]''}}}} \vspace{-.15cm} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\scriptsize{}Worked as a research and teaching fellow and taught independently. Worked as part of a team to develop and modify theories and derive hypotheses. Gathered, formatted, merged, and reshaped data from a wide variety of sources. Delivered innovative reports and datasets which exceed expectations. Presented complicated quantitative and conceptual material in a way understandable to non-experts.} \\ \hline //This is end of text color
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.2cm}
******************************End Work Experience*******************************
******************************End Work Experience*******************************
******************************End Work Experience*******************************
tex \vspace{-1.25cm} {\textcolor{`bodyhead'}{\newline {\large\textbf{Education} \newline}}}
*!DH Note 5/11/2020: Pitt
tex \vspace{-.1cm}
tex \textcolor{`bodytext'}{\footnotesize{}\textbf{ABD, Political Science ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~University of Pittsburgh~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2010-2016}} \vspace{-.05cm} \newline
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\scriptsize{}GPA 3.838} \\
tex \textcolor{`bodytext'}{\scriptsize{}Fields: Comparative Politics, International Relations, and Quantitative Methods} \\
tex \textcolor{`bodytext'}{\scriptsize{}Completed coursework, comprehensive exams, and dissertation overview defense.} \\ \hline
tex \end{tabular}
tex \vspace{.2cm}
*!DH Note 5/11/2020: UoU Masters
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\footnotesize{}\textbf{Master of Science, Political Science~~~~~~~~~~~~~~~University of Utah~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2008-2009}} \vspace{-.05cm} \newline
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\scriptsize{}GPA 3.918} \\
tex \textcolor{`bodytext'}{\scriptsize{}Fields: International Relations and Comparative Government} \\
tex \textcolor{`bodytext'}{\scriptsize{}Best graduate student paper in political science, 2009} \\ \hline
tex \end{tabular}
tex \vspace{.2cm}
*!DH Note 5/11/2020: UoU HBS
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\footnotesize{}\textbf{Bachelor of Science, Political Science~~~~~~~~~~~~~University of Utah~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2003-2007}} \vspace{-.05cm} \newline
tex \begin{tabular}{|p{5.85in}|}
tex \hline
tex \vspace{.1cm}
tex \textcolor{`bodytext'}{\scriptsize{}GPA 3.596} \\
tex \textcolor{`bodytext'}{\scriptsize{}Honors, minors in German and Chemistry} \\ \hline
tex \end{tabular}
tex \vspace{.2cm}
*********************************End Education**********************************
*********************************End Education**********************************
*********************************End Education**********************************
*!DH Note 5/12/2020: End of main section, first page
tex & \\
tex \multicolumn{5}{@{}g{8.5in}}{~} \\ [1.65cm]
*********************************End Main Block*********************************
*********************************End Main Block*********************************
*********************************End Main Block*********************************
tex \end{tabular}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Evaluation of Project-Based Curriculum for AP Government and Environmental Science \vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{}Analyst for a randomized controlled trial and quasi-experimental program evaluation. Cleaned, checked, and standardized messy and often incomplete data across five school districts across the nation over three years. Cleaned and standardized data that required significant manipulation using regular expressions to extract components, numerous reshapes, merges, and appends, extensive checking for missing or incorrect data elements, and considerable quality control checking. Developed clean tables and informative figures for reporting. } \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~Automated data checking and recoding procedures, regular expressions, management of disparate, and poorly organized data sources.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Evaluation of Project-Based Curriculum for AP Physics\vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{}Lead analyst for a quasi-experimental design project using teacher distance matching, student inverse proportional weighting, and logistic and generalized ordinal logistic regression adjustment. Received, checked, and cleaned messy and incomplete district data. Designed the evaluation plan, and conducted analysis including teacher and covariate sensitivity checks.} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~Mahalonbis distance matching, inverse proportional weighting, logisitic and generalized ordered logistic regression adjustment, teacher and model specification checks.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Student, Parent, and Staff Satisfaction Surveys \vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{}Lead analyst for annual student, parent, and staff satisfaction surveys for a large suburban school district. Receive student, staff, and parent data, clean and combine data for survey administration and analysis. Develop student sampling and distribution plans. Develop and maintain an extensive web portal that tracks response rates for each survey. Descriptive reporting, and analysis for research questions of interest to the district, including what factors relate to satisfaction and analysis of enrollment and transfer trends.} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~Structural equation models, multi-level models, sampling, confirmatory and exploratory factor analysis, automated descriptive reporting, HTML, \LaTeX{}.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Statewide Surveys of Parents of Students Receiving Special Education Services \vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{}Project director and lead analyst for two \href{https://www.spedtex.org/sites/spedtex.tea.texas.gov/files/2022-10/2021-2022%20Survey%20Results.pdf}{annual} \href{https://4.files.edl.io/82c1/01/13/22/190038-2c31c854-91a2-4651-99a8-6b8b58e68881.pdf}{statewide} surveys of parents and students receiving special education services in Texas. Request student records from the state, check for accuracy, conduct sampling, generate lists selected students in several formats, develop and maintain a web portal for school district and regional representatives that includes registration, administration information, and current response rates. Download and clean data, including checks for manipulation. Design compelling district, regional, and statewide summary reports and statewide narrative reports. Ensure the accuracy of a federal indicator reported to the Office of Special Education Programs. Designed administration plans that have resulted in increasing response rates, including developing excel lists for districts to use for mail merge or return with contact information.} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~Sampling, survey administration, automated \LaTeX{} reports, web portal, python dashboards, Qualtrics API.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Evaluation of the Relationship of Physical Fitness and Student Academic Outcomes \vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{} Analyst for a statewide analysis of student physical fitness and academic achievement. Cleaned, checked, merged, and appended three years of student administrative and physical fitness records (approximately 13 million student-year observations). Generated district and school level descriptive statistics and correlation tables, and estimated multi-level models with lagged dependent variables and difference models to assess the relationship between fitness and student outcomes.} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~Multi-level lagged dependent variable and difference models, Spearman correlation tables with custom code to increase speed for a large number of observations, descriptive statistics.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex {\textcolor{`bodytext'}{\footnotesize{\textbf{Non-Project Based Data Collection and Management\vspace{-.15cm}}}}} \newline
tex \begin{singlespace}
tex \begin{tabular}{|p{`boxsize'in}|}
tex \hline
tex \vspace{.1cm}
tex {\scriptsize{} Collect and process data beyond project-based data management. Wrote a program that lists, downloads, and cleans surveys from the Qualtrics platform (presented at the \href{https://www.stata.com/meeting/us20/}{2020 Stata Conference}). Maintain and collect publically available education and demographic data. Download and clean Texas Academic Performance Reports (TAPR) from current to 1999. Download and prepare US Census data by school district and zip code, and smaller web-scraping projects. Data management for consulting group data and PDF billing details for clients.} \\
tex {\footnotesize{}{\textcolor{`highcolor'}{\textbf{Methods:}}}{~cURL, HTML, \LaTeX{}, JavaScript, using meta-data to automate code writing, regular expressions.}} \\ \hline
tex \end{tabular}
tex \end{singlespace}
tex \vspace{.25cm}
tex & \\
tex \multicolumn{3}{@{}f{8.5in}}{\includegraphics[trim=0in 9.2in 6in 16.9in, clip, scale=.6]{${raw}raw_gtab.png}} \\
tex \end{tabular}
********************************End Second page*********************************
********************************End Second page*********************************
********************************End Second page*********************************
*=========================END DOCUMENT============================*
tex \end{document}
texdoc close
*=================================================================*
cap rm `"${latex}`docname'.pdf"'
texpdf using `"${latex}`docname'.tex"', /*copy(`"${pages}"')*/ open once