Join to fill in missing dates and counties

[wernie]

I have two datasets that I want to merge - one has data from monitors, so it may not include all dates or all counties, and the other has modeled data, so it covers all dates and all counties. I want to merge the data so that the modeled data will fill in for the dates where there are no monitoring data or for the counties where there are no monitoring data.

For example, the monitoring data may look like this (note 04Jan14 missing for county 05391 and county 05392 has no observations listed):

Date            County          Value

01Jan14       05391           5.4

02Jan14       05391           4.9

03Jan14       05391           5.1

05Jan14       05391           5.8

01Jan14       05393           10.3

02Jan14       05393           12.1

And the modeled data might look like this (note the observations with * denote those observations that I want to merge into the monitoring dataset to get a complete dataset with all dates and all counties):

Date            County          Value

01Jan14       05391           4.8

02Jan14       05391           5.0

03Jan14       05391           4.9

04Jan14       05391           5.4*

05Jan14       05391           5.8

01Jan14       05392           7.6*

02Jan14       05392           6.7*

03Jan14       05392           6.9*

04Jan14       05392           7.1*

01Jan14       05393           10.3

02Jan14       05393           12.1

I'm not sure how to merge these to get a full set of observations where I keep the monitoring values and then fill in the dates and/or counties that aren't in the monitoring datasets with the values from the modeled data. Thanks!

[Reeza]

Just do a merge by county and date. As long as the modeled data has everything it should work by default. Have you tried it yet or are you assuming you'll have a problem? I would rename the 'value' columns so they differ and that way you can tell them apart in the merge. And you can use COALESCE() to get afull data set.

data want;
merge modeled measured;
by county date;
Final_Value = coalesce(measuredValue, modeledValue);
run;

[wernie]

I did try doing something, but I don't have the code in front of me so can't quite recall what I did. I do know I was trying to do proc SQL to merge and I don't think I did it on county and date. I'll try this code and see if that gives what I need. Good point on the value columns and renaming them. Thanks!