summation with two variables (per person, per day)

Solved
Super Contributor
Posts: 328

summation with two variables (per person, per day)

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

Accepted Solutions
Solution
‎02-03-2016 11:46 AM
Posts: 1,256

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

[ Edited ]

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;``````

All Replies
Solution
‎02-03-2016 11:46 AM
Posts: 1,256

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

[ Edited ]

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;``````
Posts: 5,539

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

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)

PG
Super Contributor
Posts: 328

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

Thank you very much, FreelanceReinhard and PGStats. I will give a try!
Super User
Posts: 23,771

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

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;``````
Super User
Posts: 13,583

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

Or another approach using the data supplied by @FreelanceReinhard

``````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;``````
Super Contributor
Posts: 328

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

Thank you all. These all work!

Super User
Posts: 23,771