i have 80 data

[IdlanHnf]

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?

[Kurt_Bremser]

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:

• dataset: a table consisting of rows
• observation: a single row within the table
• variable: a single column within a row

[IdlanHnf]

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.

[RichardDeVen]

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
  ;

[IdlanHnf]

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;

[RichardDeVen · Accepted solution]

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;

[ed_sas_member]

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,