I am trying to group census tract level populations into Medical Service Study Areas (MSSA), each of which comprise of a unique, mutually exclusive list of census tracts. I have managed to get the census tract numbers organized by MSSA in a column, separated by commas:
data=have
Obs ct (char) MSSA_ID (char)
1 1210,1202,1300,1401.... 1.1
2 0701,1101,1501,1504.... 2a
3 0100,0200,7101,1700.... 2b
Now I want to efficiently apply this as a format to a data set that contains the population of each census tract:
data=census_data
census_tract population
1210 4581
1202 5142
0701 7412
1101 6233
....
So that I can get the total population of each MSSA:
data=want
MSSA_ID total_population
1.1 9723
2a 13645
....
Thanks,
D
The ungrouped data would be the version required to create a format. For example:
data format_me;
set ungrouped (rename=(ct=start mssa_id=label));
fmtname = '$mssa_gp';
run;
Once you have the proper variable names assigned, creating a format is easy:
proc format cntlin=format_me;
run;
Later, to apply the format:
format mssa_id $mssa_gp.;
It would be easier if you had ungrouped data:
CT MSSA_ID
1210 1.1
1202 1.1
1300 1.1
0701 2a
1101 2a
That's just a small sampling of course. Do you have the data available in that form?
Yes, I do have the data in that form. I was able to get the answers by merging that data set with the census_tract data set in a proc sql step, but I was hoping to be able to use a format. I will have to apply the same groups to other counts in the future and want to avoid excess merges.
The ungrouped data would be the version required to create a format. For example:
data format_me;
set ungrouped (rename=(ct=start mssa_id=label));
fmtname = '$mssa_gp';
run;
Once you have the proper variable names assigned, creating a format is easy:
proc format cntlin=format_me;
run;
Later, to apply the format:
format mssa_id $mssa_gp.;
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.