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
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;
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;
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)
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;
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;
Thank you all. These all work!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.