I’m working in SAS again, after about eight gleeful years. To be sure, I didn’t miss it, but I’d forgotten all the reasons why.

My data is in SAS, with a format catalog formats.sasb7cat. I want a fully-labeled Stata version of that data. What to do?

The following takes a SAS file and format catalog that reside in the same directory, spits out the format catalog to Stata (you’ll need to export your data in a separate step), turns the data file into a text file containing all the -label define- and -label values- statements that will reproduce the value labels present in the SAS file.

In SAS:

libname mine "c:\temp\sasxport";
libname library "c:\temp\sasxport";

proc format lib=library cntlout=fmtdata;
run;

proc export data=fmtdata
	FILE="lbls_converted.dta"
	DBMS=STATA replace;
run;

Now, escaping back to Stata. The following is a supremely lazy way of approaching the problem, but it gets the job done. For one thing, I use -preserve- and -restore- to drop all but the line in which I’m working.

use lbls_converted.dta, clear // my output from SAS

local labeldo "convert_dict.do" // this is the do-file that'll contain your generated -label- statements

tempname outfile
file open `outfile' using `labeldo', write replace

// if you've got anything "weird" in your data you want to drop, do it before this line...
qui count
local nobs=`r(N)'

by fmtname, sort: gen rowindex=_n

foreach v of varlist _all { // I had a bunch of leading / trailing whitespace -- YMMV
	cap noi replace `v' = trim(`v')
}

// this may be a peculiarity of my situation -- some, but not all, formats end in _F
// -regexr- would be smarter here, as it could trim only the *final* instance
gen vname = subinstr(fmtname,"_F","",1) // replace one instance of _F

forvalues row = 1/`nobs' {
	preserve
	qui keep in `row'
	local vname = vname
	local valname = start
	local vallab = subinstr(label, "''","",.)
	if rowindex==1 {
		*if "`labelline'"!="" file write `outfile' "`labelline'" _n
		if "`varline'"!="" file write `outfile' _n "`varline'" _n
		local varline "label values `vname' `vname'"
		file write `outfile' "label define `vname' "
	}
	* local labelline "`labelline' `valname' `vallab'"
	file write `outfile' `" `valname' "`vallab'""'
	restore
}
// end foreach

file close `outfile'

use "data_converted.dta", clear
do `labeldo'

How might this fall apart? Easily. For example, for SAS FORMATs might not be named the same as your variables. I’m sure there’s a step that could solve this easily enough, but I’m fortunate to not have had to deal with it.

I’m not a fan of manual work that can be automated. Manually processing Excel files in Stata is a drag, though, particularly when those files are designed, in part, to present data in an attractive way.

This script addresses a couple irritating issues one would otherwise have to address manually:

1. Stata lets you import data starting at the ith line, but it only imports variable names from the 1st
2. Stata abhors (forbids, even) variable names starting with a numeral or containing a hyphen.

Note that I’ve only tested this with fertility data (TFR, ASFRs, natural increase), but it works with all of them and a cursory look around makes me think it could work for any of the WPP files without too much adjustment.

Here goes:

Continue reading Reading UN World Population Prospects Data into Stata →

(it’s just the individual woman recode files, BTW)

Here it is, a variable-level file with more than 800,000 cases. It’s only about 24MB zipped, but beware: it’s about 1.6GB unzipped.

You can get the code that produced this file here. Note that you have to put up with my filename conventions, not the original DHS filenames, but it’s not hard to understand what’s what — eg coir61.dta is Colombia, a DHS VI, first revision.

There are more than 244 datasets available from DHS, but you can always run my code on the extra files and append, saving you some minutes/hours and a few dimes’ worth of electricity.

Working with cross-national survey data can be frustrating because of, among other things, incommensurability among surveys’ variable naming and value coding. Multi-national survey programs such as the Demographic and Health Surveys help provide consistent sampling designs, questionnaires and on-the-ground implementation — their work is helpful, indeed.

Inconsistencies are, for a number of reasons, essentially unavoidable. I’ve found that the way one often finds them is by discovering a problem when proceeding with an analysis that assumes all will go well. Doing better, it turns out, doesn’t require so much effort.

Stata’s built-in data documentation commands, -describe- and -codebook- among them, produce nice human-readable reports of variable and value names. They’re no good for making comparisons across lots of data files, however. Wouldn’t it be nice to have a way to easily compare, across files, the variable names, value labels, and perhaps some rough descriptive across a more or less arbitrary set of files?

Here ya go, in less than a hundred lines:

Continue reading Documenting variable and value labeling across a lot of data files →

In “Getting Correspondence from messy surveys” I took a stab at finding variables that are likely what I’m looking for. As the list of data files and my list of search terms grew, I found I needed something a little more extensible, and more general, than my ad hoc scripts.

Below is the result. Here’s what it does. Taking as parameters

• (a) a path containing your Stata data files of interest (subfolders are OK),
• (b) a list of strings for which you want matching variables (that is, whose variable and/or value labels match), and
• (c) [OPTIONAL] a list if strings whose matches you want to exclude,

the program:

(1) Uses the excellent -filelist- program written by Robert Picard to traverse the directory set in (a), and all its subfolders, creating a Stata data file containing filenames and full paths, one file per row,

(2) Opens each file identified in (1) and creates an output file, one row for each file, with a set of new variables one for each string matched, containing the names of variables returned by -lookfor-,

(3) Using the file created in (2) examines each of files found by (1) and, in a new file that has one record per variable in each file (that is, if two variables match in file A and three variables in file B, the result of this step will be a file with five rows) for each of the strings, matched,  one row per variable in each file

(4) Removes records from the file created in (3) whose variable label matches any of the strings specified in (c), writing a new file containing the reduced set.

Clearly these operations could be completed in fewer steps. I of course welcome you to make refinements and would be grateful if you shared them (with me, especially). I also use Stata’s -file- commands because I love plain text files. All of this could be done with a set of local macros plus -generate- and -append-.

Continue reading Variable exploration in many files, cont’d →