Combing data from two data sets, I don't think merge will work for me

[KevinSnapp]

Hello All,

I'm working on my first real research project and need some help. I have a big data set for a particular state's emergency room admissions for poisonings, these include drug overdoses which is what I'm interested in. My observational unit is each admission. The data I have on each observational unit is the date, time of day, severity, suspected poisoning agent. My data spans several years. I'm looking at the effectiveness of legal policies meant to curb drug related problems, overdoses being one of them. One key feature I'd like to explore is differences between urban and rural populations. The problem is my data doesn't have any such indicator. I've worked up a data set that uses census data to map zip codes to levels of urbanization. It's simply a list with zip code on the left and population information about that zip code to the right of it. I'd like to add this urbanization data to my big hospital data set. I've tried to merge a few ways, but I don't have any unique identifiers in my hospital data. If you are familiar with excel I'm sort of after a vlookup type function. I'd dump my hospital data into excel and do it with VBA but the hospital data is way to big, plus I know there has to be a way to do it in stata. What I'd like to do is this: For each observation in the hospital data I want stata to take the zip code, look up that zip code in the other data set and then add the corresponding data to the hospital data observation. Am I being clear enough? I am sure it possible, I simply can't figure it out.

Any help is appreciated.

[Rich Goldstein]

have your big data set in memory after making sure that the pop data set is sorted on zipcode; then

sort zipcode
merge m:1 zipcode using other_filename

please read the FAQ which, among other things, asks you to tell us exactly what you tried and then either give us exactly what Stata replied or tell us why it is wrong; based on your text, the code I give should work fine

[KevinSnapp]

Hi, thank you for your response,

I sorted both data sets by zip and saved them that way. I've tried


. merge m:1 zip using "\\Client\C$\Users\Kevin\Desktop\zipcode_urbanizat ion.dta"
variable zip does not uniquely identify observations in the using data
r(459);

when I have my big file open.

and


. merge m:1 zip using "\\Client\C$\Users\Kevin\Desktop\Hospital.dta"
variable zip does not uniquely identify observations in the using data
r(459);

the thing is in the zipcode_urbanization file each zipcode only appears once, so I don't understand the error msg.

[KevinSnapp]

Hi I got it to work, I had to recode some missing values! Thank you so much for your help Mr Goldstein

[Rich Goldstein]

you're welcome - and thanks for closing the thread