topic Re: summation with two variables (per person, per day) in SAS Programming

summation with two variables (per person, per day)

fengyuwuzu — Tue, 02 Feb 2016 18:13:16 GMT

I have data set with variables

Customer_ID, transaction_time, order_amount, etc

I want to:

1) count how many orders each person made per day;

2) sum the total order_amount each day for each person.

transaction_time in datetime16. format, so I can create a new variable of the day by datetime(transaction_time)

Can anyone direct me to an example how to fulfull these two goals? Thanks

Re: summation with two variables (per person, per day)

FreelanceReinh — Tue, 02 Feb 2016 18:37:28 GMT

Try this:

data have;
input Customer_ID transaction_time :datetime. order_amount;
cards;
1 01FEB16:14:30:00 120
1 01FEB16:15:05:00  80
1 02FEB16:09:20:00  30
2 01FEB16:10:00:00 100
2 01FEB16:11:00:00  45
2 01FEB16:19:00:00   5
;

proc sql;
create table want as
select Customer_ID, datepart(transaction_time) as date format=date9.,
       count(*) as num_orders,
       sum(order_amount) as total
from have
group by Customer_ID, date;
quit;

proc print data=want;
run;

Re: summation with two variables (per person, per day)

PGStats — Tue, 02 Feb 2016 18:36:50 GMT

Use proc SQL

proc sql;
create table want as
select 
	Customer_ID, 
	datepart(transaction_time) as date format=yymmdd10.,
	count(*) as nb_orders,
	sum(order_amount) as total_order
from have
group by Customer_ID, calculated date;
quit;

(untested)

Re: summation with two variables (per person, per day)

fengyuwuzu — Tue, 02 Feb 2016 18:44:33 GMT

Thank you very much, FreelanceReinhard and PGStats. I will give a try!

Re: summation with two variables (per person, per day)

Reeza — Tue, 02 Feb 2016 18:49:55 GMT

Here's a proc means solution for a different approach.

You get both output in the Results window and a dataset. If you want the Results portion suppressed you can add NOPRINT to the proc means statement.

data have;
input Customer_ID transaction_time :datetime. order_amount;
cards;
1 01FEB16:14:30:00 120
1 01FEB16:15:05:00  80
1 02FEB16:09:20:00  30
2 01FEB16:10:00:00 100
2 01FEB16:11:00:00  45
2 01FEB16:19:00:00   5
;

proc means data=have n sum nway;
class customer_ID transaction_time;

forma transaction_time dtdate9.;
var order_amount;
output out=want n=Count sum=Total;
run;

proc print data=want; run;

Re: summation with two variables (per person, per day)

ballardw — Tue, 02 Feb 2016 19:03:10 GMT

Or another approach using the data supplied by @FreelanceReinh

proc tabulate data=have;
   class customer_id transaction_time;
   format  transaction_time dtdate9.;
   var order_amount;
   tables customer_Id="Customer"*transaction_time="Date",
          n='Orders' order_amount='Total'*sum=''*f=best6.;
run;

Re: summation with two variables (per person, per day)

fengyuwuzu — Tue, 02 Feb 2016 21:03:41 GMT

Thank you all. These all work!

Re: summation with two variables (per person, per day)

Reeza — Tue, 02 Feb 2016 22:40:18 GMT

Great, please mark the question as answered.