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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.