topic Vertical Summation of observations between dates in SAS Procedures

Vertical Summation of observations between dates

deleted_user — Thu, 15 Jul 2010 16:55:46 GMT

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

Re: Vertical Summation of observations between dates

sbb — Thu, 15 Jul 2010 17:30:14 GMT

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.

Re: Vertical Summation of observations between dates

deleted_user — Thu, 15 Jul 2010 20:40:04 GMT

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

Re: Vertical Summation of observations between dates

sbb — Thu, 15 Jul 2010 21:02:35 GMT

Sorry - forgot to mention - using the LAGnn function.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search, this topic / post:

lag function site:sas.com

Re: Vertical Summation of observations between dates

deleted_user — Thu, 15 Jul 2010 22:10:40 GMT

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

Thanks,

Tim

Re: Vertical Summation of observations between dates

ArtC — Thu, 15 Jul 2010 22:16:54 GMT

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.

Re: Vertical Summation of observations between dates

SASKiwi — Thu, 15 Jul 2010 22:47:23 GMT

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;

Re: Vertical Summation of observations between dates

SASKiwi — Thu, 15 Jul 2010 22:49:32 GMT

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

Re: Vertical Summation of observations between dates

SASKiwi — Fri, 16 Jul 2010 02:16:56 GMT

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