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
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: