BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

7 REPLIES 7
FreelanceReinh
Jade | Level 19

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;
PGStats
Opal | Level 21

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
fengyuwuzu
Pyrite | Level 9
Thank you very much, FreelanceReinhard and PGStats. I will give a try!
Reeza
Super User

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;
ballardw
Super User

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;
fengyuwuzu
Pyrite | Level 9

Thank you all. These all work!

Reeza
Super User
Great, please mark the question as answered.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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