topic Re: How do I create a format for grouping results from a data set list? in SAS Programming

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

DKAllgeier — Tue, 30 May 2017 20:27:56 GMT

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,

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

Astounding — Tue, 30 May 2017 20:39:17 GMT

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?

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

DKAllgeier — Tue, 30 May 2017 20:45:33 GMT

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.

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

Astounding — Tue, 30 May 2017 20:55:59 GMT

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