summing for only two groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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: 

variableStateAbbvalue
mraAP1539
mraTG971
mraUP2750
totalAP56790
totalTG51599
totalUP88443

 

Want:

mraAP2510
mraUP2750
totalAP108389
totalUP88443

 

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

Posted in reply to putteringpluie

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;

View solution in original post


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

Re: summing for only two groups

Posted in reply to putteringpluie

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
Super User
Posts: 9,376

Re: summing for only two groups

Posted in reply to putteringpluie

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

Posted in reply to putteringpluie

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 141 views
  • 3 likes
  • 4 in conversation