BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robcafaro
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Reeza
Super User

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;

Astounding
PROC Star

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

robcafaro
Calcite | Level 5

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?

Reeza
Super User

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

robcafaro
Calcite | Level 5

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

robcafaro
Calcite | Level 5

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.

Reeza
Super User

Do you have distinct SMSA in each file?

robcafaro
Calcite | Level 5

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.

robcafaro
Calcite | Level 5

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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