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?
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;
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
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?
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.
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;
Thank you. This helps quite a bit.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.