Thank you!
@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.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.