Vertical Summation of observations between dates

[deleted_user]

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

[sbb]

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.

[deleted_user]

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

[sbb]

Sorry - forgot to mention - using the LAGnn function.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search, this topic / post:

lag function site:sas.com

[deleted_user]

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

Thanks,

Tim

[ArtC]

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.

[SASKiwi]

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;

[SASKiwi]

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

[SASKiwi]

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

Message was edited by: SASKiwi Message was edited by: SASKiwi