topic Re: How to sum values in a column and recode the values? in SAS Programming

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

wernie — Mon, 07 Oct 2019 23:50:39 GMT

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!

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

PaigeMiller — Tue, 08 Oct 2019 01:04:38 GMT

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?

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

VDD — Tue, 08 Oct 2019 02:03:33 GMT

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

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

Patrick — Tue, 08 Oct 2019 02:19:06 GMT

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

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

wernie — Tue, 08 Oct 2019 02:35:37 GMT

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.

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

Patrick — Tue, 08 Oct 2019 02:40:14 GMT

@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.html

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

Reeza — Tue, 08 Oct 2019 02:51:54 GMT

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;

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

PaigeMiller — Tue, 08 Oct 2019 10:47:17 GMT

@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+ ??