## summing for only two groups

Solved
Occasional Contributor
Posts: 9

# summing for only two groups

In 2014, the state of AP in India was split into two states AP and TG. I want to sum the values of the two states so I can compare it with 2013 values of AP. I wanted to know if there's a better way to do this than what I have right now.

Have:

 variable StateAbb value mra AP 1539 mra TG 971 mra UP 2750 total AP 56790 total TG 51599 total UP 88443

Want:

 mra AP 2510 mra UP 2750 total AP 108389 total UP 88443

Code that works for me but does not seem like an efficient one:

`proc sort data=_data0;by variable descending StateAbb;run;data _data1 ;set _data0;if StateAbb=:'AP' or StateAbb=:'TG' ;run;proc sort data=_data1;by variable descending StateAbb;run;data _data2;set _data1;by variable;if first.variable then valueap=0;valueap+value;if StateAbb=:'AP' ;run;proc sort data=_data2;by variable StateAbb;run;proc sort data=_data0;by variable StateAbb;run;data _data3(keep=variable StateAbb value);merge _data0 _data2;by variable StateAbb;if StateAbb='AP' then value=valueap;if StateAbb~='TG';run;`

Is there a better way to do this?

Thanks,

Accepted Solutions
Solution
‎01-23-2018 10:43 AM
PROC Star
Posts: 1,209

## Re: summing for only two groups

Something like this?

``````data have;
input variable\$ StateAbb\$ value;
datalines;
mra	AP	1539
mra	TG	971
mra	UP	2750
total	AP	56790
total	TG	51599
total	UP	88443
;

proc sql;
create table want as
select variable
,ifc(StateAbb="TG", "AP", StateAbb) as StateAbb
,sum(value) as sum_value
from have
group by variable, calculated StateAbb;
quit;
``````

All Replies
Solution
‎01-23-2018 10:43 AM
PROC Star
Posts: 1,209

## Re: summing for only two groups

Something like this?

``````data have;
input variable\$ StateAbb\$ value;
datalines;
mra	AP	1539
mra	TG	971
mra	UP	2750
total	AP	56790
total	TG	51599
total	UP	88443
;

proc sql;
create table want as
select variable
,ifc(StateAbb="TG", "AP", StateAbb) as StateAbb
,sum(value) as sum_value
from have
group by variable, calculated StateAbb;
quit;
``````
Super User
Posts: 9,376

## Re: summing for only two groups

Yes, code the data, then sum:

```data inter;
set _data0;
new_code=ifc(stateabb in ("AP","TG","AP",stateabb);
run;
...do sum procedures group by new_code
```
Contributor
Posts: 32

## Re: summing for only two groups

If I were doing it, I would add another variable with the original state name and then sum on that.

``````data out
set in
if StateAbb IN('AP','TG') then OrigState='AP';
run;
proc means data=out sum;class variable origstate;var value;run;
``````
☑ This topic is solved.