topic Re: Create new rows (obs) in dataset based on sum from other rows when using aggregate data in New SAS User

Create new rows (obs) in dataset based on sum from other rows when using aggregate data

MaxRoy — Thu, 18 Jul 2019 12:56:15 GMT

Hi,

I would like to create new rows in my dataset that would be based on the sum of other rows. Please note that I'm working with aggregate data.

This is what my dataset looks like:

REGION YEAR SEX TOTAL

1 2017 M 10

2 2017 M 6

3 2017 M 8

1 2017 F 11

2 2017 F 7

3 2017 F 9

1 2018 M 8

2 2018 M 10

3 2018 M 6

1 2018 F 5

2 2018 F 9

3 2018 F 7

I would like to create new rows that would combine all regions together and sum the TOTAL:

REGION YEAR SEX TOTAL

ALL 2017 M 24

ALL 2017 F 27

ALL 2018 M 24

ALL 2018 F 21

Thank you!

Add new rows based on the sum of other rows (using aggregate data)

MaxRoy — Thu, 18 Jul 2019 13:18:04 GMT

Hi,

I would like to add new rows (observations) at the end of my dataset that would be the sum of other rows.

Please note that I'm using aggregate data (not record-level).

Here's what my data looks like:

REGION YEAR SEX TOTAL

1 2017 M 10

2 2017 M 5

3 2017 M 3

1 2017 F 11

2 2017 F 4

3 2017 F 7

1 2018 M 10

2 2018 M 8

3 2018 M 6

1 2018 F 9

2 2018 F 7

3 2018 F 11

What I want is a row at the end combining all the regions together (by sex and year) with the TOTAL sum.

Something like this:

REGION YEAR SEX TOTAL

ALL 2017 M 18

ALL 2017 F 22

ALL 2018 M 24

ALL 2018 F 27

Thank you!

Re: Add new rows based on the sum of other rows (using aggregate data)

PaigeMiller — Thu, 18 Jul 2019 14:30:39 GMT

Duplicate thread

DO NOT REPLY HERE

Reply at: https://communities.sas.com/t5/New-SAS-User/Adding-rows-to-dataset-based-on-sum-from-other-rows/td-p/574558

Re: Create new rows (obs) in dataset based on sum from other rows when using aggregate data

Reeza — Thu, 18 Jul 2019 15:02:58 GMT

PROC MEANS will do this, as will SQL.

I have a few fully worked examples here that illustrate how to do this. Run the examples first, make sure they work and then try it on your own data.