BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
Of course. Why didn't I think of that!

Thanks,

Tim
ArtC
Rhodochrosite | Level 12
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
PROC Star
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
PROC Star
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
PROC Star
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 909 views
  • 0 likes
  • 4 in conversation