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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.