I have 80 data consist of 12 buyers. I would like to analyze how much every buyer has spent. How can I calculate the total amount spent by every buyer?
Use the SQL clause ORDER BY.
PROC SQL; CREATE TABLE buyerspent AS SELECT Buyer, sum('Price (RM)'n*unit) AS 'Total Spend (RM)'n FROM Payment GROUP BY Buyer ORDER BY 'Total Spend (RM)'n descending ; RUN;
SAS is different than other systems. Each variable can have a LABEL associated with it, and that label is shown during output. Meanwhile, the variable can be specified using it's simpler name.
Example
sum('Price (RM)'n*unit) AS total label='Total Spend (RM)'
^^^^^ ^^^^^^^^^^^^^^^^^^
name label
To report on multiple variables it is often better to use a procedure such as REPORT or TABULATE
Example:
data forPresentation; set myData; amount = price * unit; run; proc tabulate data=forPresentation; class buyer item; var amount;
table (buyer item), amount;
run;
Please supply example data in usable form (data step with datalines), and what you want to get out of it. DO NOT SKIP THIS.
From your description, I cannot make up what your data looks like; use SAS terminology in desctiptions:
here is my data. OrderID, Buyer, Price, Item, Unit, Courier are my variables. From this data, I have 12 buyers, I want to calculate the total amount spent by every buyer.
SQL
Compute each buyer's spending total, presuming the transaction amount would have to be computed as transaction_amount = price * unit
proc sql; create table buyer_totals as select buyer, sum(price*unit) as spend_total
from myData
group by buyer
;
thanks for your help. it's working so well. by any chance, do you know how to sort it ascending or descending? I want to sort it so that I will started with the highest revenue. And if you don't mind, is it possible if I want to try combine this two SQL run it?
PROC SQL;
CREATE TABLE buyerspent AS
SELECT Buyer, sum('Price (RM)'n*unit) AS 'Total Spend (RM)'n
FROM Payment
GROUP BY Buyer;
RUN;
PROC PRINT DATA=buyerspent;
TITLE 'Monarch Online Shop';
FOOTNOTE 'STAY AT HOME AND STAY SAFE';
RUN;
PROC SQL;
CREATE TABLE revenue AS
SELECT 'Item ID'n, sum('Price (RM)'n*unit) AS 'Total Revenue (RM)'n
FROM Payment
GROUP BY 'Item ID'n;
RUN;
PROC PRINT DATA=revenue;
TITLE 'Monarch Online Shop';
FOOTNOTE 'STAY AT HOME AND STAY SAFE';
RUN;
Use the SQL clause ORDER BY.
PROC SQL; CREATE TABLE buyerspent AS SELECT Buyer, sum('Price (RM)'n*unit) AS 'Total Spend (RM)'n FROM Payment GROUP BY Buyer ORDER BY 'Total Spend (RM)'n descending ; RUN;
SAS is different than other systems. Each variable can have a LABEL associated with it, and that label is shown during output. Meanwhile, the variable can be specified using it's simpler name.
Example
sum('Price (RM)'n*unit) AS total label='Total Spend (RM)'
^^^^^ ^^^^^^^^^^^^^^^^^^
name label
To report on multiple variables it is often better to use a procedure such as REPORT or TABULATE
Example:
data forPresentation; set myData; amount = price * unit; run; proc tabulate data=forPresentation; class buyer item; var amount;
table (buyer item), amount;
run;
Hi @IdlanHnf
As an alternative to PORC SQL, you can also use a simple PROC MEANS:
proc means data=myData nway noprint;
class buyer;
var price;
weight unit;
output out=buyer_totals (drop=_:) sum= / autoname;
run;
If you need a report -> remove the NOPRINT option
If you need a table -> keep the OUTPUT OUT= statement.
Best,
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.