Not applicable
Posts: 0

Vertical Summation of observations between dates

Hello,

My data is set up like this:

Date quantity
4-26 1
4-27 0
4-28 1
4-29 .5
4-30 0
5-1 1

what I'd like to do is create a new variable that, for each observation, equals the sum of 'quantity' for the 20 days following that observations date.

So, for example, the new variable for observation 1 (where date = 4-26) would equal sum of variable 'quantity' for observations 2 through 21.

This seems simple enough but I just cant figure out a sensible way to do this. Thank you!

Tim
Super Contributor
Posts: 3,176

Re: Vertical Summation of observations between dates

Sort by "date" in descending-order (presuming it's a SAS numeric DATE variable or convert your char string to a SAS DATE using INPUT). Then accumulate a new SAS numeric variable while keeping track with a count variable -- you can use the RETAIN statement to retain the accumulation variable across DATA step passes.

Scott Barry
SBBWorks, Inc.
Not applicable
Posts: 0

Re: Vertical Summation of observations between dates

Sorry if I seem a bit thick, but I don't quite understand what you suggest to "finish it off"

I have sorted, created a variable that accumulates the quantity variable, and created a count variable, but how can i then use the count variable to create a new variable that which shows the 20 day totals for each date?

Thanks for taking the time to respond.

Tim
Super Contributor
Posts: 3,176

Re: Vertical Summation of observations between dates

Sorry - forgot to mention - using the LAGnn function.

Scott Barry
SBBWorks, Inc.

lag function site:sas.com
Not applicable
Posts: 0

Re: Vertical Summation of observations between dates

Of course. Why didn't I think of that!

Thanks,

Tim
Valued Guide
Posts: 653

Re: Vertical Summation of observations between dates

The LAG function will do the trick, but I have always had trouble with the logic to make it work. An alternative is to use an array as a stack. The following calculates the total of the current and previous 2 values (instead of 20).. To make the array act like a stack the index is circular (MOD function).
[pre]
data quantity;
input md \$ quantity;
date = mdy(scan(md,1),scan(md,2),2010);
format date date9.;
datalines;
4-26 1
4-27 1.1
4-28 1.2
4-29 1.5
4-30 2.5
5-1 3.5
5-2 4.5
5.3 5.5
run;

* Three day running total;
data total;*(keep=date quantity total);
set quantity nobs=nobs;
* dimension of array is number to be totaled;
retain temp0-temp2;
array stack {0:2} temp0-temp2;
index = mod(_n_,3);
stack{index} = quantity;
total = sum(of temp;
run;

proc print data=total;
run;
[/pre]

Does anyone know of a true stack routine in the DATA step? They exist otherplaces within SAS.
Super User
Posts: 3,913

Re: Vertical Summation of observations between dates

I would be tempted to pre-process the data first converting your date to a SAS date, creating two new dates to use with the summing, then use SQL like so:

data new;
set old;
sas_date = mdy(scan(date,1,'-'), scan(date,2,'-'), 2010);
start_date = sas_date + 1;
end_date = sas_date + 20;
run;

proc sql;
create data new2 as
select n1.sas_date
,sum(n2.value)
from new as n1,
new as n2
where n1.start_date >= n2.sas_date
and n1.end_date <= n2.sas_date
group by n1.sas_date
;
quit;
Super User
Posts: 3,913

Re: Vertical Summation of observations between dates

Oops, forgot the GROUP BY

I would be tempted to pre-process the data first converting your date to a SAS date, creating two new dates to use with the summing, then use SQL like so:

data new;
set old;
sas_date = mdy(scan(date,1,'-'), scan(date,2,'-'), 2010);
start_date = sas_date + 1;
end_date = sas_date + 20;
run;

proc sql;
create data new2 as
select n1.sas_date
,sum(n2.value)
from new as n1,
new as n2
where n1.start_date >= n2.sas_date
and n1.end_date
group by n1.sas_date
Super User
Posts: 3,913

Re: Vertical Summation of observations between dates

Oops 2, my SQL got chopped.

Oops, forgot the GROUP BY

I would be tempted to pre-process the data first converting your date to a SAS date, creating two new dates to use with the summing, then use SQL like so:

data new;
set old;
sas_date = mdy(scan(date,1,'-'), scan(date,2,'-'), 2010);
start_date = sas_date + 1;
end_date = sas_date + 20;
run;

proc sql;
create data new2 as
select n1.sas_date
,sum(n2.value)
from new as n1,
new as n2
where n1.start_date ge n2.sas_date
and n1.end_date le n2.sas_date
group by n1.sas_date