BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
putteringpluie
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
  
ebowen
Quartz | Level 8

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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