I have a file where I need to expand to include all counties.
The first file that has the data I need (start and end month and days) does NOT have all counties. It looks like this:
STATE COUNTY FIPS5 START_MONTH START_DAY END_MONTH END_DAY
01 01 3 1 10 31
01 073 01073 1 1 12 31
02 02 4 1 10 30
02 090 02090 1 1 12 31
04 04 1 1 12 31
04 005 04005 1 1 12 31
04 007 04007 1 1 12 31
I need to merge this with a file that has all state and county codes to fill in values for ALL counties. I just want to be able to expand the counties in the new file and wherever the county value is blank, I want to apply the start_month, start_day, end_month, and end_day values to those expanded counties, while leaving the values for the rows where county DOES have a value in the data file.
Essentially, I want to expand the new file to look something like this (dots are just to continue rows up to observation where something would be different):
STATE COUNTY FIPS5 START_MONTH START_DAY END_MONTH END_DAY
01 001 01001 3 1 10 31
01 003 01003 3 1 10 31
01 005 01005 3 1 10 31
01 007 01007 3 1 10 31
… …….. . . .. ..
01 073 01073 1 1 12 31
02 013 02013 4 1 10 30
02 016 02016 4 1 10 30
… …….. . . .. ..
02 090 02090 1 1 12 31
I hope this makes sense. How can I expand to fill in all county values? I'm thinking to take a county reference file and merge by county and that might fill in the counties where they aren't listed, but I still need to retain the counties that have a different start and end month and day. Thank you!
To begin with you don't have County in the first data set so it is not obvious what is going on.
I can guess that your Fips5 is related to county but you should really provide a complete description.
And since your second set is missing values for county as presented it gets even harder to tell what is going on.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.