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,
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;
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;
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.