BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DKAllgeier
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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
Fluorite | Level 6

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
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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