topic Re: Summing over the column in SAS Procedures

Summing over the column

Statsconsultancy — Tue, 16 Feb 2010 17:33:09 GMT

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.

Re: Summing over the column

RickM — Tue, 16 Feb 2010 18:24:16 GMT

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

Re: Summing over the column

RickM — Tue, 16 Feb 2010 18:26:01 GMT

You should actually sort "by ID Date Time" to make sure it is all in order.

Re: Summing over the column

deleted_user — Wed, 17 Feb 2010 06:45:37 GMT

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.