I have a data sorted by two variables (comp and ind), how can I sum sales by ind?

[xmg2088]

A   X  8

A   X  6

A   Y  9

A   Y  2

B   X  7

B   X  5

...

How can I calculate a new variable that sum the same A, first X + first Y (8+9), second X + second Y (6+2). results look like this:

A   (8+9=)17

A  (6+2=)8

B  ...

Thanks for anyone's help.

[ed_sas_member · Accepted solution]

Hi @xmg2088 

I am thinking about something like this:

data have;
	input comp $ ind $ value;
	datalines;
A   X  8
A   X  6
A   Y  9
A   Y  2
B   X  7
B   X  5
;
run;

data have_fl;
	set have;
	by comp ind;
	if first.ind then num_obs = 0;
	num_obs + 1;
run;

proc sort data=have_fl;
	by comp num_obs;
run;

proc means data=have_fl sum noprint;
	var value;
	by comp num_obs;
	output out=want (drop=_: num_obs) sum=sum;
run;

proc print;

[xmg2088]

This works, brilliant! Thanks so much!

[ed_sas_member]

Awesome ! You're welcome @xmg2088

[novinosrin]

data have;
input comp $ ind $ sales ;
cards;
A   X  8

A   X  6

A   Y  9

A   Y  2

B   X  7

B   X  5
;


data Want ;
 if _n_=1 then do;
  dcl hash H (ordered:'a') ;
  h.definekey  ("_n_") ;
  h.definedata ("sum") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do until(last.comp);
  do _n_=1 by 1 until(last.ind);
   set have;
   by comp ind;
   if h.find() ne 0 then sum=sales;
   else sum=sum(sales,sum);
   h.replace();
  end;
 end;
 do While(hi.next()=0);
   output;
 end;
 h.clear();
 keep comp sum;
run;

[xmg2088]

Thank you @novinosrin, very elegant!