topic Re: i have 80 data in SAS Programming

i have 80 data

IdlanHnf — Fri, 19 Jun 2020 09:44:08 GMT

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?

Re: i have 80 data

Kurt_Bremser — Fri, 19 Jun 2020 09:51:34 GMT

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

Re: i have 80 data

IdlanHnf — Fri, 19 Jun 2020 10:17:10 GMT

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.

Re: i have 80 data

RichardDeVen — Fri, 19 Jun 2020 10:29:41 GMT

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
  ;

Re: i have 80 data

IdlanHnf — Fri, 19 Jun 2020 11:37:46 GMT

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;

Re: i have 80 data

ed_sas_member — Fri, 19 Jun 2020 11:42:15 GMT

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,

Re: i have 80 data

RichardDeVen — Fri, 19 Jun 2020 13:10:04 GMT

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;