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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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

 


Accepted Solutions
Solution
‎05-30-2017 05:39 PM
Super User
Posts: 5,513

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

Posted in reply to DKAllgeier

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

View solution in original post


All Replies
Super User
Posts: 5,513

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

Posted in reply to DKAllgeier

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?

Occasional Contributor
Posts: 5

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

Posted in reply to Astounding

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.

Solution
‎05-30-2017 05:39 PM
Super User
Posts: 5,513

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

Posted in reply to DKAllgeier

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 287 views
  • 1 like
  • 2 in conversation