Select which observations to include in merge

[wernie]

Hi all,

I have two datasets that I want to merge together. One has values where they were obtained from a monitor (so it's only for a subset of counties within a state), the other has values where they were obtained from modeling (so it's for all counties within a state).

I want to be able to create one final dataset where it has all counties where it uses the monitoring value if there's a value for that in the monitoring dataset and it uses the model value if there's no value for that in the monitoring dataset.

An example of the datasets is below as well as what the final dataset should look like. Model_Flag (0 = monitor, 1 = model). Thank you!

Monitor

Year           State              County         State_County             Pop         Value1          Value2      Model_Flag

2014            36                     001                   36001                 274658          0                0                      0

2014            36                     005                   36005                 837456          1             7261                   0

2014            36                     013                   36013               1846372          3            958754                0

2014            36                     041                   36041                 85643            2             54678                 0

Model

Year           State             County          State_County             Pop         Value1       Value2             Model_Flag

2014            36                     001                   36001             274658          0                    0                   1

2014            36                     007                   36007             726785          4                63932               1

2014            36                     017                   36017             529676          2                13462               1

Merged dataset (keeping mon value where available for county and filling in modeled value where not available in monitored dataset)

Year           State            County           State_County           Pop           Value1       Value2        Model_Flag

2014            36                     001                   36001             274658          0                 0                       0

2014            36                     005                   36005             837456          1               7261                   0

2014            36                     007                   36007             726785          4             63932                   1

2014            36                     013                   36013           1846372          3            958754                  0

2014            36                     017                   36017             529676          2            13462                    1

2014            36                     041                   36041             85643            2             54678                   0

[Kurt_Bremser · Accepted solution]

Sorz both datasets by year, state, county, then do

data want;
merge
  model
  monitor
;
by year state county;
run;

The order of datasets in the merge defines precedence when there are matching observations.

This method requires that observations are unique with regard of the by variables, in both datasets.