topic Re: I have a data sorted by two variables (comp and ind), how can I sum sales by ind? in SAS Programming

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

xmg2088 — Sun, 03 May 2020 15:28:17 GMT

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.

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

ed_sas_member — Sun, 03 May 2020 15:45:25 GMT

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;

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

xmg2088 — Sun, 03 May 2020 15:50:44 GMT

This works, brilliant! Thanks so much!

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

ed_sas_member — Sun, 03 May 2020 16:04:08 GMT

Awesome ! You're welcome @xmg2088

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

novinosrin — Sun, 03 May 2020 16:07:04 GMT



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;

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

xmg2088 — Sun, 03 May 2020 18:27:02 GMT

Thank you @novinosrin, very elegant!