summation with two variables (per person, per day)

[fengyuwuzu]

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

[FreelanceReinh · Accepted solution]

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;

[PGStats]

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)

[fengyuwuzu]

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

[Reeza]

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;

[ballardw]

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;

[fengyuwuzu]

Thank you all. These all work!

[Reeza]

Great, please mark the question as answered.