topic Re: summing for only two groups in SAS Programming

summing for only two groups

putteringpluie — Tue, 23 Jan 2018 15:19:58 GMT

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,

Re: summing for only two groups

PeterClemmensen — Tue, 23 Jan 2018 15:28:43 GMT

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;

Re: summing for only two groups

RW9 — Tue, 23 Jan 2018 15:29:35 GMT

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

Re: summing for only two groups

ebowen — Tue, 23 Jan 2018 15:35:35 GMT

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;