Sum up lagged values

[aaou]

Thank you!

[aaou]

Hi Xia,
I have more question. If I am to change the time window, for example if I am to look back at 4 years, instead of 3 years; how would the code change? Where are the places that I need to change and what are the changes?

I'm completely new to programming so I really do appreciate your patience.

[Reeza]

@aaou Fastest way to learn is to get code, change a line and see what happens.

Since you know 3 is your aggregate before, consider finding all values of 3 in the code and changing them to 4. Make sure to identify any other indexes that may also need to be changed - ie a 2 may need to be incremented to 3. 

Also, make sure you test your code and results thoroughly. I like to take a small subset, test it, then apply it to the full dataset. And then recheck a bunch of random cases. 

[aaou]

Hi, that's precisely what I did. But it didn't give me the deisred output. Perhaps I haven't changed all the relevant parameters.

[ballardw]

I suspect that you may need to address some issues related to the actual number of previous values available in your data. In the start of this you had "look at the previous 3 except when there were  only 2 or 1 do something else"

Now by increasing the look back for 4 you need to add how to deal with the case when there are only 3 previous values.

[Ksharp]

Chang the following:


proc transpose data=have(obs=0 drop=id year) out=x;
run;
proc sql noprint;
select _name_ into : names separated by ',' from x;

select catt('array _',_name_,'{0:3} _temporary_;')   * <---------;
       into : arrays separated by ' '
   from x;
   
select catt('sum_',_name_,'=sum(of _',_name_,'{*});')
   into : sums separated by ' ' 
   from x;
   
select catt('_',_name_,'{mod(n,4)}=',_name_,';') into : v separated by ' '      * <---------;
 from x;
quit;

proc sql;
create table temp as
 select a.*,&names
  from (select * from
  (select distinct id from have),(select distinct year from have)) as a
  left join have as b 
   on a.year=b.year and a.id=b.id
    order by a.id,a.year;
quit;

data want;
 set temp;
 by id;
 &arrays
 if first.id then n=0;
 n+1;
 &sums
 &v
 if n gt 4;     * <---------;
 keep id year sum_:;
 run;

[aaou]

Thanks a lot!