Horizontal summation of observations

[clancaster]

I modified data into a new data set to look like this;

ID Trans Sales

01 1 $135.00
01 1 $75.00
01 1 $140.00
01 2 $140.00
02 1 $220.00
03 1 $135.00
03 1 $125.00
03 2 $80.00

I have to sum the sales by the ID. I hit a wall and would appreciate any feedback as I am stuck on how to properly syntax summing sales by ID. Here is my code I have that attempts that:

data employee_sales;
merge datain.sales datain.parts;
by part_no;
if quantity = . then delete;
drop part_no price quantity;
sales= quantity*price;
total_sales= SUM(sales);
run;
proc sort;
by descending ID ;
run;
proc print data=employee_sales noobs;
format ID z2. sales dollar8.2 ;
title 'Employee Sales';
run;

and the output for that

ID trans sales total_sales
03 1 $135.00 135
03 1 $125.00 125
03 2 $80.00 80
02 1 $220.00 220
01 1 $135.00 135
01 1 $75.00 75
01 1 $140.00 140
01 2 $140.00 140

It just repeats itself, does anyone have any idea what I am doing wrong?

[novinosrin]

what do you mean by Horizontal summation?

I am unable to understand your expected output 

I could see the sales dataset and where's the parts dataset

[Reeza]

Unfortunately you have the wrong approach. SUM will sum all values within a ROW, not in a column. You want PROC MEANS. 

Here's a quick example on how it works, the last one includes one that has a grouping variable. 

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

---

@clancaster wrote:

I modified data into a new data set to look like this;

ID Trans Sales

01 1 $135.00
01 1 $75.00
01 1 $140.00
01 2 $140.00
02 1 $220.00
03 1 $135.00
03 1 $125.00
03 2 $80.00

I have to sum the sales by the ID. I hit a wall and would appreciate any feedback as I am stuck on how to properly syntax summing sales by ID. Here is my code I have that attempts that:

data employee_sales;
merge datain.sales datain.parts;
by part_no;
if quantity = . then delete;
drop part_no price quantity;
sales= quantity*price;
total_sales= SUM(sales);
run;
proc sort;
by descending ID ;
run;
proc print data=employee_sales noobs;
format ID z2. sales dollar8.2 ;
title 'Employee Sales';
run;

and the output for that

ID trans sales total_sales
03 1 $135.00 135
03 1 $125.00 125
03 2 $80.00 80
02 1 $220.00 220
01 1 $135.00 135
01 1 $75.00 75
01 1 $140.00 140
01 2 $140.00 140

It just repeats itself, does anyone have any idea what I am doing wrong?

---

[clancaster]

Oh thanks for the insight

[VDD]

you have provided only 1 table as incoming data, and you are using a merge why?

Please provide better detail and code that represents the data samples you provide.

[Tom · Accepted solution]

If you want total sales by ID then cannot do it in a data step that sorted by PART_NO.

You can use RETAIN and SUM(total_sales,sales).  Or just use a sum STATEMENT.

data employee_sales_raw;
  merge datain.sales datain.parts;
  by part_no;
  if quantity = . then delete;
  drop part_no price quantity;
  sales= quantity*price;
run;
proc sort;
  by id descending;
run;
data employee_sales ;
  set employee_sales_raw ;
  by id descending ;
  total_sales + sales ;
  if first.id then total_sales=0;
run;

[clancaster]

Thank you. This helps quite a bit.