Hello, I am relatively new to SAS and have viewed the various posts on the lag subject by group processing (using arrays, proc expand (don't have), etc.). I have below my code that derives a rolling sum which moves forward by day with a fixed window (which could be anything, but in this case is 20,30,40,...100). I am looking to build the code that will enable each separate group to be lagged by the corresponding value from the LOC table (which contains the Loc and the corresponding lag value). However, this is test code for a single item and eventually we will be incorporating thousands of items to view the max Qty over a rolling period. I am not a fan of arrays since it requires breaking out each separate group into a column, which if 1000 Items were needed you would have 1000*N different Loc's all in need of separate lag values so you would have an insane amount of columns. I would prefer to keep it all aligned moving down ONE column with each separate lag by group if that makes sense? I am looking for a forward rolling sum which subtracts out the last cumulative sum value using a fixed window that varies across groups. Below gets the job done, but once again it will not be able to adjust to more observations. If you could help that would be great! PROC IMPORT DATAFILE = "/folders/myfolders/SAS Practice/CPS_MODEL_PRACT/sample_loop_CPS.xlsx" DBMS = XLSX OUT = LOOP_SAMPLE REPLACE; RUN; PROC SQL; CREATE TABLE LOC AS SELECT DISTINCT Loc, RLT AS RLT FROM LOOP_SAMPLE; QUIT; PROC SORT DATA = LOOP_SAMPLE; BY Loc; RUN; DATA HAVE; SET LOOP_SAMPLE; COUNT + Qty; IF Loc ="DDSP" THEN A = LAG20(COUNT); IF Loc ="DDCN" THEN A = LAG30(COUNT); IF Loc ="DDCT" THEN A = LAG40(COUNT); IF Loc ="DDDC" THEN A = LAG50(COUNT); IF Loc ="DDDE" THEN A = LAG60(COUNT); IF Loc ="DDDK" THEN A = LAG70(COUNT); IF Loc ="DDJC" THEN A = LAG80(COUNT); IF Loc ="DDJF" THEN A = LAG90(COUNT); IF Loc ="DDSI" THEN A = LAG100(COUNT); ROLLING_TOTAL = COUNT - A; RUN; PROC MEANS DATA = HAVE MAX; BY Loc; VAR ROLLING_TOTAL; RUN;
... View more
