Help using Base SAS procedures

Question about Conditional Merging

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Question about Conditional Merging

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


Accepted Solutions
Solution
‎10-08-2013 01:07 PM
Super User
Posts: 5,498

Re: Question about Conditional Merging

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

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Question about Conditional Merging

Posted in reply to robcafaro

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;

Solution
‎10-08-2013 01:07 PM
Super User
Posts: 5,498

Re: Question about Conditional Merging

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

Occasional Contributor
Posts: 6

Re: Question about Conditional Merging

Posted in reply to Astounding

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?

Super User
Posts: 19,770

Re: Question about Conditional Merging

Posted in reply to robcafaro

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

Occasional Contributor
Posts: 6

Re: Question about Conditional Merging

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

Occasional Contributor
Posts: 6

Re: Question about Conditional Merging

Posted in reply to robcafaro

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.

Super User
Posts: 19,770

Re: Question about Conditional Merging

Posted in reply to robcafaro

Do you have distinct SMSA in each file?

Occasional Contributor
Posts: 6

Re: Question about Conditional Merging

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.

Occasional Contributor
Posts: 6

Re: Question about Conditional Merging

Posted in reply to robcafaro

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?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 352 views
  • 6 likes
  • 3 in conversation