topic Re: Question about Conditional Merging in SAS Procedures

Question about Conditional Merging

robcafaro — Tue, 08 Oct 2013 16:28:05 GMT

Hello! I'm having some issues conditionally merging two datasets. This is what I want to do:

I have an hmda data set (> 2 million observations) and ahs data set (~60,000 observations). I want to merge by metropolitan statistical area, race, sex, and here's where the conditional part comes in:

The last variable I want to join by is when ahsincome = hmdaincome or ahsincome > hmdaincome. I can get up to this point with this code:

proc sql;

create table hmda_ahs as

select L.*, R.*

from ahs as L

left join clean_hmda as R

on L.smsa=R.smsa

and L.race=R.race

and L.sex=R.sex

and (L.ahsincome = R.hmdaincome or L.ahsincome > R.hmdaincome);

quit;

Now, obviously, this leaves me with a merged data set (hmda_ahs) that's ridiculously large. What I really want to end up with is a merged data set where

(L.ahsincome = R.hmdaincome or L.ahsincome > R.hmdaincome) is true, but only retains the minimum difference between ahsincome and hmdaincome across the matches, so it cuts down on all of the duplicate matches. Is this a possible condition to add to the proc sql (I've tried, but can't seem to put that condition in), can I do it with a merge statement, or where else should I be looking? Or, is it possible to keep only the observations that meet my criterion in the humongous merged data set hmda_ahs?

I look forward to some ideas. Thanks!

Rob

rcafaro2@uncc.edu

Re: Question about Conditional Merging

Reeza — Tue, 08 Oct 2013 16:41:39 GMT

Untested at all....but I think your last grouped condition is also equal to >= rather than separate conditions with an or. You may need to break that up or put it in a having clause.

An easy alternative is order by the variables and then taking the first one in a datastep.

proc sql;

create table hmda_ahs as

select L.*, R.*, min(ahsincome-hmdaincome) as min_income

from ahs as L

left join clean_hmda as R

on L.smsa=R.smsa

and L.race=R.race

and L.sex=R.sex

and L.ahsincome >= R.hmdaincome

group by smsa, race, sex

where (ahsincome-hmdaincome)=calculated min_income ;

quit;

Re: Question about Conditional Merging

Astounding — Tue, 08 Oct 2013 17:07:26 GMT

A small tweak ... when using GROUP BY and subsetting the RESULTS rather than subsetting the incoming data, use HAVING instead of WHERE.

Re: Question about Conditional Merging

robcafaro — Tue, 08 Oct 2013 17:25:19 GMT

I just tried that code. It makes sense, but I'm getting an ambiguous reference error message (column smsa, race, and sex are in more than one table). How do I adjust for that?

Re: Question about Conditional Merging

Reeza — Tue, 08 Oct 2013 17:30:08 GMT

Refer to the them with the appropriate alias, eg. L.smsa

Re: Question about Conditional Merging

robcafaro — Tue, 08 Oct 2013 17:35:07 GMT

ha. geez. yea, that worked. Thanks you both!

Re: Question about Conditional Merging

robcafaro — Tue, 29 Oct 2013 18:28:29 GMT

Ok, so I just tried to replicate this code with an updated dataset, but for some reason the results aren't right (as in, the final number of observations is way more than it should be). So, this time, I'm merging HMDA data (1,906,993 observations) with AHS data (22,511 observations). This is the code I am using:

Proc sql;

create table AHS_HMDA as

select L.* R.*, min(hmdaincome-ahsincome) as min_income

from ahs as L

left join hmda as R on

L.smsa=R.smsa

and L.race=R.race

and L.sex=R.sex

and L.ahsincome <= R.hmdaincome

group by L.smsa, L.race, L.sex

having (hmdaincome-ahsincome) = calculated min_income;

quit;

This process is now resulting in over 100,000 observations, when there should not be any more observations than the AHS dataset (22,511). I'm basically trying to find matches between the two datasets, and only keeping the minimized difference between the income values of both datasets.... Last time I ran the code we had discussed before, I was left with a lot less observations. Not sure why it's different.

Re: Question about Conditional Merging

Reeza — Tue, 29 Oct 2013 18:48:19 GMT

Do you have distinct SMSA in each file?

Re: Question about Conditional Merging

robcafaro — Tue, 29 Oct 2013 23:10:26 GMT

It's a many to many merging process, so similar smsa's are found across both datasets, but multiple matches are made. There is no unique identifier in either data set. One other way I'm seriously considering to merge the data sets is to create bounds around AHS income (say, keep the minimum observation of income in which hmdaincome falls within a +/-$2000 range around ahsincome). This would lead to more observations, but not more than what I have within the ahs dataset itself. Any help on that would be much appreciated as well.

Re: Question about Conditional Merging

robcafaro — Wed, 30 Oct 2013 00:33:42 GMT

More specifically, what I would like to do is place a bandwidth restriction around ahsincome that hmdaincome may fall into, then keep the kernel-weighted average within that bandwidth (say, +/- $2000 around ahsincome). This should bring my number of observations down significantly, but still yield (from previous research) close to 1000 observations. Any pointers?