PII_stata_scan.do

/*********************************************************************************************************************************************
Description: This file will scan all .dta files within a directory and all of its subdirectories for potential PII. Potential PII includes 
variables with names or labels containing any of the strings in global search_string. The program decodes all encoded numeric variables (i.e. 
those with value labels or those created using the command "encode") to create string variables, which are searched along with all original 
string variables for variables with string lengths greater than 3 (or user-defined length). Flagged variables are saved to pii_stata_output.csv. 
Inputs: Path to top directory.
Outputs: pii_stata_output.csv (saved to current working directory)
Date Last Modified: February 3, 2022
Last Modified By: Jack Cavanagh (jcavanagh@povertyactionlab.org)
**********************************************************************************************************************************************/

version 15.1
clear all
set more off 
set maxvar 32767

cd "" // CHANGE PATH TO WHERE YOU WANT TO SAVE pii_stata_output.csv

global directory_to_scan "" // SET THIS DIRECTORY TO THE ONE YOU WANT TO SCAN (change options at botton of do-file)


***Command "filelist" required:
capture ssc install filelist

****************************** ADD OR REMOVE SEARCH STRINGS AS NEEDED: ******************************
#delimit ;
global search_strings
	address
	bday
	beneficiary
	birth 
	block
	cell
	census
	city
	compound
	coord
	district
	email
	fax
	gender
	gps
	landline
	latitude
	location
	longitude
	municipality
	name
	network
	panchayat
	parish
	phone 
	precinct
	sex
	school
	social
	street
	subcountry
	territory
	village
	zip
	nama
	desa
	kecamatan
	garis
	lintang
	bujur
	ulang
	tahun
;
global indirect_search_strings
	child
	community
	country
	daughter
	father
	husband
	house
	mother
	spouse
	wife

;
global strict_search_strings
	url
	son
	dob
	loc
;
#delimit cr;
*****************************************************************************************************

capture program drop pii_scan
program pii_scan
	syntax anything(name=search_directory id="path of directory to search")[, remove_search_list(string) add_search_list(string) ignore_varname(string) string_length(integer 3) samples(integer 5) time]
	/*
	EXPLANATION OF INPUTS:
		search_directory = path of directory to search 
		remove_search_list = list of strings to remove from the search list (e.g. if you don't want to search for string with "zip" or "wife" in the name or label, use 
							 option remove_search_list(zip wife)
		add_search_list = list of strings to add to the search list (e.g. if you also want to search for "person" in name/label, use option add_search_list(person)
		ignore_varname = A list of strings such that if there are any variables flagged with any of these strings in the VARIABLE NAME, they will NOT be output to the CSV file 
				(e.g. if you don't want any variables with the word "materials" to be output to pii_stata_output.xlsx, use option "ignore(materials)"). 
				NOTE: This does not ignore the word if it is only found in the variable label.
		string_length = the cutoff length for the strings you want to be flagged. The default is 3 (i.e. strings with lengths greater than 3 will be output to CSV file)
		samples = number of samples to output to CSV, default is 5
		time = display time takes to run do-file (start time and end time)
	*/
	if !missing("`time'") {
		local start_time = c(current_time)
	}
	
	*make list of user defined search strings to ignore lowercase:
	local ignore_strings
	foreach search_string of local remove_search_list {
		local string_lower = lower("`search_string'")
		local ignore_strings "`ignore_strings' `string_lower'"
	}
	*make list of user defined search strings to add lowercase:
	local add_strings
	foreach search_string of local add_search_list {
		local string_lower = lower("`search_string'")
		local add_strings "`add_strings' `string_lower'"
	}
	
	*Remove strings user defined from search list:
	global final_search_list : list global(search_strings) - ignore_strings
	
	*Add strings user defined to search list:
	global final_search_list : list global(final_search_list) | add_strings
	*Make sure list only contains unique values: 
	global final_search_list : list uniq global(final_search_list)
	
	tempfile file_list 
	filelist, directory(`search_directory') pattern("*.dta")
	gen temp="/"
	egen file_path = concat(dirname temp filename)
	keep file_path
	save `file_list'

	qui count
	local total_files = `r(N)'
	forvalues i=1/`r(N)' {
		local file_`i' = file_path[`i']
	}

	capture file close output_file
	file open output_file using pii_stata_output.csv, write replace text
	
	foreach header in "file" "var" "varlabel" "most freq value" "unique values" "total obs" "first reason flagged"{
		file write output_file _char(34) `"`header'"' _char(34) ","
	}
	forvalues i=1/`samples' {
		file write output_file _char(34) `"samp`i'"' _char(34) ","
	}
	file write output_file _n
	
	local total_variables = 0 // used to count/display total number of variables at end
	local total_variables_flagged = 0 // used to count/display number of FLAGGED variables at the end

	local backtick `"`"'
	qui count
	forvalues i=1/`r(N)' {
		use "`file_`i''", clear
		qui count 
		local N = r(N) // USED WHEN OUTPUTING TO CSV
		***Initialize locals:
		local decoded_vars_original
		local decoded_vars_renamed
		local vars_output_csv
		local strings_to_output
		local string_vars
		local numeric_vars 
		local all_vars
		local flagged_vars
		local reason_out
		
		*Drop any variables with strings in the user defined "ignore" list:
		foreach ignore_string of local ignore_varname {
			foreach var of varlist * {
				local var_name = lower("`var'")
				local lower_ignore_string = lower("`ignore_string'")
				local ignore_name_pos = strpos("`var_name'","`lower_ignore_string'")
				if `ignore_name_pos'!=0 {
					drop `var'
				}
			}
		}
		
		foreach var of varlist * {
			local ++total_variables
			local all_vars "`all_vars' `var'"
		}
		*** Decode all of the string variables that are encoded (this creates a string variable that takes on the values of the value labels) 
		foreach var of varlist * {
			*** If the variable name is longer than 31 character, need to substring to get the first 31 letters so can add on DCD
			if length("`var'")>31 {
				local var_prefix = substr("`var'",1,31)
			}
			else {
				local var_prefix "`var'"
			}
			capture decode `var', gen(`var_prefix'DCD)
			if _rc==0 { // if the variable is successfully decoded (i.e. it has a value label):
				*** Make sure that the decoded variable is not missing for all obs 
				*   - if it is missing then there is a value label associated with this variable but none of the numeric values the variable takes correspond to 
				*   the strings in the value label:
				qui count
				local n1=r(N)
				qui count if mi(`var_prefix'DCD)
				local n2=r(N)
				
				*If the decoded variable is NOT missing for all observations, add it to list of decoded variables: 
				if `n1'!=`n2' {
					local decoded_vars_original "`decoded_vars_original' `var'"
					local decoded_vars_renamed "`decoded_vars_renamed' `var_prefix'DCD"
				}
				else {
					drop `var_prefix'DCD
				}
			}
		}
		if "`decoded_vars_original'"!="" {
			local j=0
			foreach orig_var of local decoded_vars_original {
				local ++j
				local k=0
				foreach new_var of local decoded_vars_renamed {
					local ++k
					if `j'==`k' {
						drop `orig_var'
						rename `new_var' `orig_var'
					}
				}
			}
		}
		
		***Get list of all string variables (this will include original string variables and the decoded variables) 
		foreach var of varlist * {
			capture confirm string var `var'
			if _rc==0 {
				local string_vars "`string_vars' `var'"
			}
			else {
				local numeric_vars "`numeric_vars' `var'"
			}
		}
		
		
		*** Search through ALL the variables to see if there is a PAIR of variables for lat/lon:
		*First see if there are two variables "lat" and "lon" anywhere in the datafile:
		local lat = 0
		local lon = 0
		foreach var of local all_vars {
			if lower("`var'")=="lat" {
				local lat = 1
				local lat_var "`var'"
				cap local `var'r "Latitude variable found"
			}
			if lower("`var'")=="lon" {
				local lon = 1
				local lon_var "`var'"
				cap local `var'r "Longitude variable found"

			}
		}
		if `lat'==1 & `lon'==1 {
			local flagged_vars "`lat_var' `lon_var'"
		}
		
		local total_vars = 0
		foreach var of local all_vars {
			local ++total_vars
			local lab: variable label `var'
			local var_label = lower("`lab'")
			local var_name = lower("`var'")
			
			local var`total_vars' "`var_name'"
			local var_orig_case`total_vars' "`var'"
			local var_label`total_vars' "`var_label'"
		}
		local var_num = 0
		foreach var of local all_vars {
			local ++var_num
			local prev_var_num = `var_num' - 1
			local next_var_num = `var_num' + 1
			
			if `var_num'!= 1 {
				local prev_var "`var`prev_var_num''"
				local prev_var_orig_case "`var_orig_case`prev_var_num''"
				local prev_var_label "`var_label`prev_var_num''"
			}
			else {
				local prev_var
				local prev_var_orig_case
				local prev_var_label
			}
			if `var_num' != `total_vars' {
				local next_var "`var`next_var_num''"
				local next_var_orig_case "`var_orig_case`next_var_num''"
				local next_var_label "`var_label`next_var_num''"
			}
			else {
				local next_var
				local next_var_orig_case
				local next_var_label
			}
				
			local lab: variable label `var'
			local var_label = lower("`lab'")
			local var_name = lower("`var'")
			
			*If "lat" is in the variable name or label, see if "lon" is in the next or previous variables name or label:
			if strmatch("`var_name'","*lat*")==1 | strmatch("`var_label'","*lat*")==1 {
				*if it's in the PREV variable name or label:
				if strmatch("`prev_var'","*lon*")==1 |  strmatch("`prev_var_label'","*lon*")==1 {
					display "lat/lon variable found: `var' (label = `var_label')"
					
					*ADD CURRENT VARIABLE AND PREVOUS VARIABLE TO FLAGGED:
					local flagged_vars "`flagged_vars' `var' `prev_var_orig_case'"
					cap local `var'r "Lat/Lon Combination found"
					local `prev_var_orig_case'r "Lat/Lon Combination found"
				}
				*if it's in the NEXT variable name or label:
				if strmatch("`next_var'","*lon*")==1 | strmatch("`next_var_label'","*lon*")==1 {
					display "lat/lon variable found: `var' (label = `var_label')"
					* ADD CURRENT VARIABLE AND NEXT VARIABLE TO FLAGGED:
					local flagged_vars "`flagged_vars' `var' `next_var_orig_case'"
					cap local `var'r "Lat/Lon Combination found"
					local `next_var_orig_case'r "Lat/Lon Combination found"

				}
			}
		}
		
		*Remove duplicates in list of flagged vars:
		local flagged_vars : list uniq flagged_vars
		
		*Search through the remaining variables to see if there are variables for degree minute second. Only output the variables if all three are present, otherwise they are not likely to be PII:
		*initialize locals:
		foreach gps_string in degree minute second {
			local `gps_string' = 0
			local `gps_string'_vars
			local `gps_string'r
		}
		local gps_var_search : list all_vars - flagged_vars
		foreach var of local gps_var_search {
			local var_name = lower("`var'")
			local lab: variable label `var'
			local var_label = lower("`lab'")
			cap local `var'r "GPS variables found"
			foreach gps_string in degree minute second {
				if strmatch("`var_name'","*`gps_string'*")==1 | strmatch("`var_label'","*`gps_string'*")==1 { 
					local `gps_string' = 1
					local `gps_string'_vars "``gps_string'_vars' `var'"
				}
			}
		}
		if `degree'==1 & `minute'==1 & `second' == 1 {
			local flagged_vars "`flagged_vars' `degree_vars' `minute_vars' `second_vars'"
		}
		
		*Remove duplicates in list of flagged vars:
		local flagged_vars : list uniq flagged_vars
			
	
		***Search through the variables and see if there are any of the PII search words in the variable names or labels:
		***Only look through variables that havent been assigned to be output to CSV sheet already
		
**************************************************************************************
********************** New section -- Fall 2020 ********************************************************************

		local small_search : list all_vars - flagged_vars		
			foreach var of local small_search {
			local lab: variable label `var'
			local var_label = lower("`lab'")
			local var_name = lower("`var'")
			local keep_searching = 1
			cap local `var'r
			foreach search_string of global indirect_search_strings {
				if `keep_searching' == 1 {
					local search_string = lower(`"`search_string'"')
					***Look for string in variable name:
					local name_pos = strpos("`var_name'","`search_string'")
					***Look for string in variable label: 
					local label_pos = strpos("`var_label'","`search_string'")
					if `name_pos'!=0 | `label_pos' !=0 {
						display "search term `search_string' found in `var' (label = `var_label')"
						cap local `var'r "search term `search_string' found in `var' (label = `var_label')"
						
						local flagged_vars "`flagged_vars' `var'"
						local keep_searching = 0
					}
				}
			}	
			tempvar no_miss
			if `keep_searching' ==0{
				egen `no_miss' = total(!missing(`var'))
				quietly: duplicates report `var'
				if `r(unique_value)' <=2 {
					if `no_miss' != `r(unique_value)'{
						local rem_vars "`rem_vars' `var'"
					}
				}
				else{
					local decod = strpos("`decoded_vars_original'", "`var_name''")
					if `decod' !=0{
						tempvar deco_temp
						tempvar group_temp
						tempvar perc_temp
						tempvar `n_miss'
						qui egen `deco_temp' = group(`var')
						bysort `deco_temp': gen `group_temp' = _N
						qui egen `n_miss' = total(!missing(`var'))
						qui gen `perc_temp' = `group_temp'/`n_miss'
						qui sum `perc_temp'
						if r(min) >= .05 {
						local rem_vars "`rem_vars' `var'"
						}
					}
					else{
						cap confirm string var `var'
						if _rc!=0{
							tempvar dup
							sort `var'
							quietly by `var': gen `dup' = cond(_N==1,0,_n)
							quietly: sum `dup'
							if `r(mean)' >0 {
								local rem_vars "`rem_vars' `var'"
							}
							else{
								if strpos(string(`var'),"."){
								local rem_vars "`rem_vars' `var'"
							}
						}
					}
				}
			}
		}
	}
	    local flagged_vars : list flagged_vars - rem_vars	
***************************************************************************************
		local search_list : list all_vars - flagged_vars
		*local flagged_vars "`strings_to_output'"
		foreach var of local search_list {
			local lab: variable label `var'
			local var_label = lower("`lab'")
			local var_name = lower("`var'")
			local keep_searching = 1
			cap local `var'r
			foreach search_string of global final_search_list {
				if `keep_searching' == 1 {
					local search_string = lower(`"`search_string'"')
					***Look for string in variable name:
					local name_pos = strpos("`var_name'","`search_string'")
					***Look for string in variable label: 
					local label_pos = strpos("`var_label'","`search_string'")
					if `name_pos'!=0 | `label_pos' !=0 {
						display "search term `search_string' found in `var' (label = `var_label')"
						cap local `var'r "search term `search_string' found in `var' (label = `var_label')"
						
						local flagged_vars "`flagged_vars' `var'"
						local keep_searching = 0
					}
				}
			}
			
			* If the variable hasnt been flagged yet look through the strict search terms list:
			if `keep_searching' == 1 {
				foreach str of global strict_search_strings {
					if `keep_searching' == 1 {
						*Search the variable name for the exact match or a match at the beginning or end of the variable name or in the middle separated by "_":
						if "`var_name'"=="`str'" | strmatch("`var_name'","*`str'")==1 | strmatch("`var_name'","`str'*")==1 | strmatch("`var_name'","*_`str'_*")==1 {
							display "Strict search term `str' found in `var' (label = `var_label')"
							cap local `var'r "Strict search term `str' found in `var' (label = `var_label')"
							local flagged_vars "`flagged_vars' `var'"
							local keep_searching = 0
						}
						
						*labels: - search for the full word in the label:
						if "`var_label'"=="`str'" | strmatch("`var_label'","* `str' *")==1 | strmatch("`var_label'","* `str'")==1 | strmatch("`var_label'","`str' *")==1 {
							display "Strict search term `str' found in `var' (label = `var_label')"
							cap local `var'r "Strict search term `str' found in `var' (label = `var_label')"

							local flagged_vars "`flagged_vars' `var'"
							local keep_searching = 0
						}
					}
				}	
			}	
		}
		*** If a binary variable does not have a search term in it and it has at least one value with >=2 responses then it is removed from the search
		local binary_vars : list all_vars - flagged_vars
		foreach var of local binary_vars{
			tempvar nonmiss
			qui egen `nonmiss' = total(!missing(`var'))
			quietly: duplicates report `var'
			if `r(unique_value)' <=2 & `nonmiss' != `r(unique_value)'{
				local remove_vars "`remove_vars' `var'"
			}
		}
		*** If a previously encoded variable does not have a search term in it and it has <=10 unique values then it is removed from the search
		local decoded_vars_new: list decoded_vars_original - flagged_vars
		foreach var of local decoded_vars_new{
			tempvar dec_temp
			qui egen `dec_temp' = group(`var')
			qui sum `dec_temp'
			if `dec_temp' <= 10{
				local remove_vars "`remove_vars' `var'"
			}
		}
		local vars_cat: list all_vars - flagged_vars
		foreach var of local vars_cat{
				tempvar temp
				tempvar grp_temp
				tempvar perc_temp
				tempvar un_miss
				qui egen `temp' = group(`var')
				bysort `temp': gen `grp_temp' = _N
				qui egen `un_miss' = total(!missing(`var'))
				qui gen `perc_temp' = `grp_temp'/`un_miss'
				qui sum `perc_temp'
				if r(min) >= .05{
				local remove_vars "`remove_vars' `var'"
				}
		}
		local string_vars_new: list string_vars - remove_vars
		*** Search through the STRING variables that havent been flagged by the name/label search to find variables with lengths greater than 3:
		local string_vars_to_search : list string_vars_new - flagged_vars
		foreach var of local string_vars_to_search {
			tempvar temp1
			cap local `var'r
			qui gen `temp1' = length(`var') // string length 
			qui sum `temp1'
			if `r(max)'>`string_length' {
				local var_name = lower("`var'")
				local lab: variable label `var'
				display "`var' (label = `lab') has length > `string_length'"
				cap local `var'r "`var' (label = `lab') has length > `string_length'"
				local flagged_vars "`flagged_vars' `var'"
			}
			drop `temp1'
		}
		
		***Make sure list of flagged variables does not contain repeated variables:
		local flagged_vars : list uniq flagged_vars
		
		***Dont output variable to list if all observations are missing:
		local flagged_vars_copy `flagged_vars'
		qui count 
		local total_obs = r(N)
		foreach var of local flagged_vars_copy {
			qui count if missing(`var')
			if r(N)==`total_obs' {
				display "`var' is missing for all observations - don't output to CSV"
				local flagged_vars : list flagged_vars - var
			}
		}
		
		*Drop the non-flagged variables:
		capture keep `flagged_vars'
		qui duplicates drop
		
		***Output the flagged variables to csv file: 
		foreach var of local flagged_vars {
			local ++total_variables_flagged
			tempvar obsnm_temp temp2 temp3 temp4 temp5
			qui egen `temp2' = group(`var') // group var
			qui egen `temp3' = mode(`temp2'), maxmode // mode of GROUP
			qui egen `temp4' = tag(`temp2')
			qui gen `temp5' = `temp4'*`temp2' // tag*group = 1 for first obs in group 1, 0 for second obs in group 1, 2 for first obs in group 2, etc
			***First column=path
			file write output_file _char(34) `"`file_`i''"' _char(34) ","
			***Second column=variable nam
			file write output_file _char(34) `"`var'"' _char(34) ","
			***Third column=label
			local lab: variable label `var'
			local lab : subinstr local lab "`backtick'" "" // remove backtick 
			***Remove double quotation marks from label which messes up writing to single cells of CSV file:
			local lab = subinstr(`"`lab'"',`"""',"",.)
			file write output_file _char(34) `"`lab'"' _char(34) ","
			***Fourth column=most frequent value  -- mode = `temp3' - value where tag*group (`temp5') = mode
			qui gen `obsnm_temp'=_n
			qui sum `obsnm_temp' if `temp3'==`temp5'
			
			local most_freq_value = `var'[`r(mean)']
			local most_freq_value : subinstr local most_freq_value "`backtick'" "" // remove backtick 
			*Remove double quotation marks which mess up writing to single cells of CSV file:
			local most_freq_value = subinstr(`"`most_freq_value'"',`"""',"",.)
			file write output_file _char(34) `"`most_freq_value'"' _char(34) ","
			***Fifth column = ratio of num diff values/num obs
			***Fifth column = # of unique values
			qui sum `temp2'
			file write output_file _char(34) `"`r(max)'"' _char(34) ","
			local num_unique_values = `r(max)' // save this to use for writing samples below
			***Sixth column = total observations
			***NOTE: `N' comes from "qui count" when file is first opened
			file write output_file _char(34) `"`N'"' _char(34) ","
			****Seventh column = reason why variable was flagged
			file write output_file _char(34) `"``var'r'"' _char(34) ","
			***Eighth column = samp1 (nonmissing) --> N+7th column = sampN (nonmissing):
			***First sort by tag*group:
			*** Only do the sorting if samples>0:
			if `samples'>0 {
				gsort - `temp5'
				forvalues m=1/`samples' {
					local samp`m' = `var'[`m']
				}
				local num_samples_output = min(`samples',`num_unique_values') // If there are only 2 unique values, only write 2 samples (samp1 samp2) 
				forvalues sampnum=1/`num_samples_output' {
					*Remove double quotation marks which mess up writing to single cells of CSV file:
					local samp`sampnum' : subinstr local samp`sampnum' "`backtick'" "" // remove backtick 
					local samp`sampnum' = subinstr(`"`samp`sampnum''"',`"""',"",.)
					file write output_file _char(34) `"`samp`sampnum''"' _char(34) ","
				}
			}
			drop `obsnm_temp' `temp2' `temp3' `temp4' `temp5'
			drop `var'
			file write output_file _n
		}
	}
	file close output_file
***** This section takes the outputted file, reads it back into Stata, and then combines variables that have the same name and label across datasets	
clear
import delimited "pii_stata_output.csv"
**Cleaning the file names by deleting the directory (would be the same for all)
replace file = subinstr(file,"$directory_to_scan/", "",.)
**generating marker for if the variable is found in multiple datasets
sort var varlabel
quietly by var varlabel:  gen Multiple_datasets = cond(_N==1,0,_n)
quietly: sum Multiple_datasets
if `r(max)' > 0{
gen max = `r(max)'
tempfile master 
save "`master'", replace
**adding all of the files the variable is found into the "file" field
keep if Multiple_datasets>=1
sort var varlabel
egen group = group(var varlabel)
quietly: sum max
local count = r(max)
if `r(max)'>5{
	forvalues x=1/5{
	replace file = file + ";" + file[_n+`x'] if group == group[_n+`x']
}
}
else{
forvalues x=1/`count'{
	replace file = file + ";" + file[_n+`x'] if group == group[_n+`x']
}
}
*dropping the duplicate
drop if Multiple_datasets>1
merge 1:m var varlabel using "`master'"
drop _merge group max
}
duplicates drop
**Turning the dummy variable into "yes/no"
tostring Multiple_datasets, replace
replace Multiple_datasets = "Yes" if Multiple_datasets == "1"
replace Multiple_datasets = "No" if Multiple_datasets == "0"
**Dropping irrelevant variables
drop v13
**Labeling the reason a variable was flagged if its variable name was too long previously 
replace firstreasonflagged = "Variable name too long: search string found" if firstreasonflagged == ""
**Re-exporting the report
export delimited "pii_stata_output.csv", replace

	display ""
	display "------------------------------------------------------------"
	if !missing("`time'") {
		display ""
		display "START TIME = `start_time'"
		display "END TIME = " c(current_time)
	}
	display ""
	display "FILES SCANNED = `total_files'"
	display "VARIABLES SCANNED = `total_variables'"
	display "VARIABLES WITH POTENTIAL PII = `total_variables_flagged'"
	display ""
	display "------------------------------------------------------------"
end
pii_scan ${directory_to_scan}