topic Re: Running total in SAS Data Management

Running total

shivkr — Tue, 10 Jun 2014 12:42:34 GMT

Hello,

How to get the running total of a variable upto n records

group by some variable?

I want to create a new variable by doing the sum of first 4 records order by date value and group by color.

date	color	value
3/17/2014	white	3
3/18/2014	white	2
3/19/2014	white	1
3/20/2014	white	2
3/21/2014	white	0
3/24/2014	white	38
3/25/2014	white	38
3/26/2014	white	38
3/17/2014	black	1
3/18/2014	black	3
3/19/2014	black	2
3/20/2014	black	1
3/21/2014	black	0
3/24/2014	black	6
3/25/2014	black	7
3/26/2014	black	6

Re: Running total

LinusH — Tue, 10 Jun 2014 13:06:16 GMT

You can use the data step with set, by, retain and first.-logic to accomplish this.

Re: Running total

Astounding — Tue, 10 Jun 2014 13:21:34 GMT

Judging by the language you use to describe the problem, you are more accustomed to using SQL than a DATA step. SQL will not be a reliable tool for this application, as it does not guarantee the order of extracted records. Instead, you can try:

proc sort data=have;

by color date;

run;

data want;

set have;

by color date;

if first.color then do;

total = value;

record_count=1;

end;

else do;

total + value;

record_count + 1;

end;

if record_count = 4;

run;

This approach outputs nothing for colors that have fewer than 4 records. Slight changes to the logic can change that, but this DATA step illustrates the basic tools you will need to start with.

Re: Running total

Ksharp — Tue, 10 Jun 2014 13:47:44 GMT

data have;
input date : mmddyy10.     color $     value ;
format      date mmddyy10.;
cards;
3/17/2014     white     3
3/18/2014     white     2
3/19/2014     white     1
3/20/2014     white     2
3/21/2014     white     0
3/24/2014     white     38
3/25/2014     white     38
3/26/2014     white     38
3/17/2014     black     1
3/18/2014     black     3
3/19/2014     black     2
3/20/2014     black     1
3/21/2014     black     0
3/24/2014     black     6
3/25/2014     black     7
3/26/2014     black     6
;
run;
data want;
 set have;
 if color ne lag(color)  then do; n=0;sum=0;end;
 n+1;sum+value;
 if n in (1:4);
 drop n;
run;

Xia Keshan

Re: Running total

stat_sas — Tue, 10 Jun 2014 13:56:58 GMT

proc sort data=have;
by color date;
run;

data want;
set have;
if mod(_n_,4)=1 then grp+1;
run;

proc sql;
select color, sum(value) from want
where grp in (1,3)
group by color,grp;
quit;

Re: Running total

Reeza — Tue, 10 Jun 2014 14:40:01 GMT

If you have SAS/ETS licensed look into proc expand.

Re: Running total

shivkr — Tue, 10 Jun 2014 15:11:07 GMT

Thanks all for your suggestion. Is it possible to get the same result using PROC SQL?

Re: Running total

Astounding — Tue, 10 Jun 2014 16:20:26 GMT

No, it's not possible using PROC SQL. SQL by its nature does not guarantee the order of the records that it retrieves. It has no guaranteed way of determining which are the four records that you want for each COLOR.

Perhaps I shouldn't say it's impossible. Perhaps you can devise a method to identify the records that you want to retrieve. But nobody that I know has ever accomplished that. There is an undocumented feature (MONOTONIC) that could help in theory ... if only it were guaranteed to work every time. But it is not guaranteed so even if you got it to work properly once, it might not work properly the next time.

You just might be stuck having to learn to program in a DATA step for some types of problems.

Re: Running total

Reeza — Tue, 10 Jun 2014 17:09:21 GMT

Yes, but not recommended.

If you're familiar with SQL and working with a SQL database you can use pass-through logic and program as if you were in a native DB.

Otherwise create a row counter, via a data step and then merge via the row counter. More steps and less efficient.

Re: Running total

stat_sas — Tue, 10 Jun 2014 21:30:26 GMT

You can try this. As mentioned in above responses MONOTONIC ( ) function is undocumented so results should be interpreted carefully.

proc sql;

create table one as

select *, monotonic () as cnt

from have

order by color,date;

create table want as

select *, (cnt - min(cnt) + 1 ) as cnt_grp

from one

group by color

order by cnt;

select color,sum(value) as total_4 from want

where cnt_grp<5

group by color;

quit;

Re: Running total

Haikuo — Wed, 11 Jun 2014 02:53:24 GMT

if you have to resort to Proc SQL, there is a documented solution to get the sequence number:

ods _all_ close;

ods output sql_results=want;

proc sql number;

select * from have;

quit;

ods output close;

ods listing;

Haikuo

Re: Running total

stat_sas — Wed, 11 Jun 2014 13:49:47 GMT

Thanks Haikuo.