How do I create a format for grouping results from a data set list?

[DKAllgeier]

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

[Astounding]

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?

[DKAllgeier]

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.

[Astounding · Accepted solution]

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.;