topic Horizontal summation of observations in SAS Programming

Horizontal summation of observations

clancaster — Fri, 09 Nov 2018 22:13:58 GMT

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?

Re: Horizontal summation of observations

novinosrin — Fri, 09 Nov 2018 22:21:44 GMT

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

Re: Horizontal summation of observations

Reeza — Fri, 09 Nov 2018 22:22:28 GMT

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?

Re: Horizontal summation of observations

VDD — Fri, 09 Nov 2018 22:28:02 GMT

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.

Re: Horizontal summation of observations

clancaster — Fri, 09 Nov 2018 22:44:56 GMT

Oh thanks for the insight

Re: Horizontal summation of observations

Tom — Fri, 09 Nov 2018 23:03:39 GMT

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;

Re: Horizontal summation of observations

clancaster — Sat, 10 Nov 2018 19:50:22 GMT

Thank you. This helps quite a bit.