DATA Step, Macro, Functions and more

summation with two variables (per person, per day)

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

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
Trusted Advisor
Posts: 1,115

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;

View solution in original post


All Replies
Solution
‎02-03-2016 11:46 AM
Trusted Advisor
Posts: 1,115

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;
Respected Advisor
Posts: 4,640

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: 312

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

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

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: 10,458

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: 312

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

Thank you all. These all work!

Super User
Posts: 17,731

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

Great, please mark the question as answered.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 294 views
  • 0 likes
  • 5 in conversation