Will Doerner´s notes - v useful STATA video tutorials

On cleaning up data, reshaping, merging, appending

http://youtu.be/xyEs2zCgnVg

• ltrim command is to trim the data to the left when you have "leading blanks" e.g. if you have [space]y1993 then you type 

replace y1993=ltrim [y1993] 
i.e. you specify in the squar brackets what you want to keep of the obs data
rtrim command does the same, just from the right side - what they call "trailing blanks"

• ignore so this is useful. Use after a "replace" command if e.g. you want to destring a bunch of data entries but then ignore if they contain a non numeric symbol like a $ sign or maybe a % sign. So you write 

destring y1993, replace ignore ("$")

• foreach in action - so how to repeat (or loop) the command. So in this case, it is so as to repeat the trimming on both sides of the observations for all of the data set, then destringing it, whilst keeping but ignoring dollar signs in data, dropping missing observations in the dataset, and then destringing again, incase the first destring didnt work for numbers that had , or . or missing...though I feel he could just miss the first destring rather than doing it twice (?). This is the code - the 2005 specifies the range i.e. that it should run the trimming code from 1994 through to 2005

forvalues i=1994(1)2005 {
replace y`i'=ltrim [y`i']
replace y`i'=rtrim [y`i']
destring y`i', replace ignore  ("$") 
drop if y`i'=="" & County == ""

destring y`i', replace ignore  ("$" "," ".")
       }


(NB. did not know that when obs change from string to numeric values, they also change from red to black)

• Then he generates an ID number _n is useful because it just numbers every observation in the data set from 1 to the final observation - so do this after you ve sorted the data by the variable you want to be numbered, in this case county

sort county 
gen id=_n

Then, I dont really get why you would want to do this, but you can also then generate a value thats plus one or minus one like this
gen id=[_n+1] 

• Then to reshape data long he does...it is still a little puzzling to me how stata knows to separate the data in the way it ultimately displays it. I think it knows that long is any variable that begins with y in the data set. Those variables in brackets then become the horizontal variables and all of the variables along the horizontal that begin with y end up in one single column. So it does this by matching i and j with y whereever i and j correspond to y. 

reshape long y, i(id County) j(year)

• Then with the merge he differentiates between many to many merge and the normal merge which produced a completely different merge results. He says that the reason why the normal merge produces so many _merge=2 is because he does the merge by county and year, but there are years in the second data set that don't exist in the first (so really append would be better in this case?) but then he does the m:m merge, and it says that there are no incomplete merges...so I don't really understand how this happened. 

• notes - you can make notes on the data. To display existing notes you put notes list and to drop notes you just put notes drop _all (if you want to drop all notes). To make a new note you put 

notes: this data set was last updated on ddmmyy

(then note list again will show you the notes you´ve created - seems to not matter, notes or note)

• append is from min 8.26 useful but don´t use v often can go back and write down later if needed. 

***

• On concatenating - you can do this first in excel or in stata this is how http://statadaily.wordpress.com/2010/10/05/concatinating-string-variables/

Also on missing values here
http://www.williamdoerner.com/stata/xtmissingfill

He also has his own website

http://www.williamdoerner.com/