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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.