Help using Base SAS procedures

Summing over the column

Reply
Occasional Contributor
Posts: 16

Summing over the column

I have the following data:
Data mydata;
Date Time Sales ID
03/01/2008 08:39:18 22.99 0
03/01/2008 08:41:21 12.99 0
03/01/2008 08:42:13 12.99 0
03/02/2008 08:07:19 22.99 0
03/02/2008 09:06:20 22.99 0
02/03/2008 11:51:18 12.99 0
02/01/2008 14:31:42 22.99 1
02/07/2008 12:17:27 22.99 1
02/07/2008 13:42:27 22.99 1
02/07/2008 13:58:37 12.99 1
02/07/2008 15:03:23 12.99 1

I will like to compute sales by time Vi(A) the agent (ID) accrued at time say ti(A). That is I will to have something that looks like this

Date Time Sales ID totalsales
03/01/2008 08:39:18 22.99 0 22.99
03/01/2008 08:41:21 12.99 0 35.98
03/01/2008 08:42:13 12.99 0 48.97
03/02/2008 08:07:19 22.99 0 22.99
03/02/2008 09:06:20 22.99 0 45.98
02/03/2008 11:51:18 12.99 0 12.99
02/01/2008 14:31:42 22.99 1 22.99
02/07/2008 12:17:27 22.99 1 22.99
02/07/2008 13:42:27 22.99 1 45.98
02/07/2008 13:58:37 12.99 1 68.97
02/07/2008 15:03:23 12.99 1 91.96

I will appreciate it if you can help.
Regular Contributor
Posts: 165

Re: Summing over the column

Posted in reply to Statsconsultancy
First you should sort by ID. Then in a data step do something like:

retain totalsales;
if first.id then total sales=0;
totalsales=totalsales+sales;

*not tested
Regular Contributor
Posts: 165

Re: Summing over the column

You should actually sort "by ID Date Time" to make sure it is all in order.
N/A
Posts: 0

Re: Summing over the column

Posted in reply to Statsconsultancy
Hi,

You might want to try the code below, and check whether you get the right output. Here at different dates of a single agent you get the sales cumulative as it starts with that date.

It got a bit complicated in the end, but it would work.
Any other better solutions are welcome.


data test;
input date: ddmmyy10. time: time8. sales id;
cards;
03/01/2008 08:39:18 22.99 0
03/01/2008 08:41:21 12.99 0
03/01/2008 08:42:13 12.99 0
03/02/2008 08:07:19 22.99 0
03/02/2008 09:06:20 22.99 0
02/03/2008 11:51:18 12.99 0
02/01/2008 14:31:42 22.99 1
02/07/2008 12:17:27 22.99 1
02/07/2008 13:42:27 22.99 1
02/07/2008 13:58:37 12.99 1
02/07/2008 15:03:23 12.99 1
;
run;
proc print;
run;
proc sort data=test;
by id date time;
run;

data final;
set test;
by ID date;
if first.id then
if first.date then
sales_cummulative=sales;
else
sales_cummulative+sales;
else if first.date then sales_cummulative=sales;
else
sales_cummulative+sales;
run;

proc print;
run;



Thanks,
Saurabh.
Ask a Question
Discussion stats
  • 3 replies
  • 160 views
  • 0 likes
  • 3 in conversation