How to sum values in a column and recode the values?

[wernie]

Hi,

I have population data for different age groups and I want to collapse the age groups to <50, 50-65, and 65+ to get the total population for each of those age groups.

I have:

GEO_ID            Age_Group                   Pop

1                           1                                50

1                           2                                80

1                           3                                90

1                           4                                150

1                           5                                180

1                           6                                205

1                           7                                258

1                           8                                375

1                           9                                383

1                           10                                865

So I basically want to get the following:

GEO_ID            Age_Group                                                  Pop

1                           1 (new value for <50)                               5004

1                           2 (new value for 50-65)                            6493

1                           3 (new value for 65+)                               3026

Thanks!

[PaigeMiller]

How do age_group 1 through 10 in your original data get assigned to the output age_groups which are 1, 2 or 3?

Why is GEO_ID even needed here?

[VDD]

@wernie more information is needed...

do we reject age_group 4--10?

do you have sample data for geo_id 1 where the age_group is in 1, 2 or 3 that support the results for that sample Pop value that you have of 5004?

Please provide sample data that match's your expected results and include all data columns that are relevant to the determination.

[Patrick]

@wernie 

If you can map your current age groups into the ones you're after then creating a simple format for this mapping would do the job. 

Then just use this format to either create a new variable or use your current age group variable but apply the format for processing. Many SAS procedures allow you to do so and will analyse the data using the formatted values.

[wernie]

I’m just trying to collapse the age groups to get <50, 50-65, 65+ and get the population total for those groups. The GEO_ID a unique ID so each GEO_ID needs to have the age groups collapsed and populations summed.

[Patrick]

@wernie 

Then just use Proc Format for recoding the age groups.

https://blogs.sas.com/content/iml/2019/06/10/5-reasons-to-use-proc-format-to-recode-variables-in-sas... 

[PaigeMiller]

---

@wernie wrote:
I’m just trying to collapse the age groups to get <50, 50-65, 65+ and get the population total for those groups. The GEO_ID a unique ID so each GEO_ID needs to have the age groups collapsed and populations summed.

---

This doesn't tell us anything more than the original message. How do you collapse groups numbered 1 to 10 into <50, 60-65, 65+ ??

[Reeza]

Create a format.

You can run the code below and see the output in your SAS session to see how you need to modify your code.

*create the format;
proc format;
value age_group
low - 13 = 'Pre-Teen'
13 - 15 = 'Teen'
16 - high = 'Adult';
run;

title 'Example of an applied format';
proc print data=sashelp.class;
format age age_group.; *applies the format;
run;


data class;
set sashelp.class;
age_category = put(age, age_group.); *creates a character variable with the age category;
label age_category = 'Age Category'; *adds a nice label for the printed output;
run;

title 'Example of creating a new variable with the format';
proc print data=class label;
run;

*show format used directly;

proc freq data=sashelp.class;
table age / out= formatted_age;
format age age_group.;
run;