topic Re: Summarize: once per group in SAS Programming

Summarize: once per group

Konkordanz — Tue, 27 Jul 2021 09:33:29 GMT

Hi,

in my dataset I´ve some data of several regions of the world. Each region is found multiple times in my dataset. Column A is the region, column B contents the values I want to summarize and column C includes the population of the respective region. The population also occur several times, but its always the same. This one should not be summarized.

Data i Have:

	Value	Population
Region A	5	82 321 556
Region B	6	50 879 514
Region C	7	9 054 876
Region B	4	50 879 514
Region C	6	9 054 876
Region A	8	82 321 556
Region B	4	50 879 514
Region C	1	9 054 876
Region B	3	50 879 514
Region A	9	82 321 556
Region C	4	9 054 876
Region A	2	82 321 556

Data I want:

	Value	Population
Region A	24	82 321 556
Region B	17	50 879 514
Region C	18	9 054 876

Do you know a shot way to do this? Thank you!

Re: Summarize: once per group

andreas_lds — Tue, 27 Jul 2021 10:29:10 GMT

Some options:

sort by region, then use a data step to calculate the sum. first / last will be necessary, "first" to reset the sum, "last" to output the observation.
use proc summary to calculate the sum, and maybe id-statement to preserve "population"

If you want code, post data in usable form.

Re: Summarize: once per group

Kurt_Bremser — Tue, 27 Jul 2021 10:41:55 GMT

If population is reliably the same for all observations within a region, then you can do this in PROC SUMMARY:

proc summary data=have nway;
class region population;
var value;
output
  out=want (drop=_freq_ _type_)
  sum()=
;
run;

But why do you have a redundant value in your dataset in the first place?

Re: Summarize: once per group

Konkordanz — Tue, 27 Jul 2021 12:35:43 GMT

Thanks for your answers. Sorry, but I should specify my concern:

Each year (2019, 2020) has several regions with specific population-size. The population should be summarized per year, but only one time per region (transfer 1x into the line of the year, otherwise the population in general would be too big).

The values of Account1-AccountN should be summarized per year over all regions.

Data I have:

Year	Region	Population	Account1	Account2	AccountN
2019	RegionA	82320980	.	8	7
2019	RegionA	82320980	4	.	8
2019	RegionB	50879514	.	6	.
2019	RegionB	50879514	2	.	.
2019	RegionC	43025905	4	.	5
2019	RegionC	43025905	.	2	.
2020	RegionA	82320520	5	4	.
2020	RegionA	82320520	1	.	8
2020	RegionB	50879320	.	3	5
2020	RegionB	50879320	4	4	.
2020	RegionC	43025782	7	.	6
2020	RegionC	43025782	5	9	.

Here is the Code for the data:

data have;
input year $ Region $ population Account1 Account2 AccountN;
datalines;
2019 RegionA 82320980 . 8 7
2019 RegionA 82320980 4 . 8
2019 RegionB 50879514 . 6 .
2019 RegionB 50879514 2 . .
2019 RegionC 43025905 4 . 5
2019 RegionC 43025905 . 2 .
2020 RegionA 82320520 5 4 .
2020 RegionA 82320520 1 . 8
2020 RegionB 50879320 . 3 5
2020 RegionB 50879320 4 4 .
2020 RegionC 43025782 7 . 6
2020 RegionC 43025782 5 9 .
;
run;

What I want is that:

Year	Population	Account1	Account2	AccountN
2019	176 226 399	10	16	20
2020	176 225 622	22	20	19

Sorry for beeing concrete that late :)! Thank you for helping me.

Re: Summarize: once per group

GraphGuy — Tue, 27 Jul 2021 14:57:00 GMT

Or, using SQL ...

data foo;
input region $ 1-8 value population;
datalines;
Region A	5	82321556
Region B	6	50879514
Region C	7	9054876
Region B	4	50879514
Region C	6	9054876
Region A	8	82321556
Region B	4	50879514
Region C	1	9054876
Region B	3	50879514
Region A	9	82321556
Region C	4	9054876
Region A	2	82321556
;
run;

proc sql;
create table summarized_data as
select unique region, population, sum(value) as sum_value
from foo
group by region, population;
quit; run;

proc print data=summarized_data; run;