How to do a rolling sum with dynamic fixed window that varies across groups?

[rich93johnson]

Thanks to both of you!!!!!! I believe either solution will work. I will try and integrate. 

[Tom]

Can you post sample data (preferable for a simplified version).  Also not as an XSLX file. The XLSX you attached is not viewable in the forum (perhaps it is too large or uses Excel features the viewer doesn't support).  Many people cannot download documents.

Make a simple example using "lags" of just 2 or 3 to get the idea across, not hundreds. Post the data as a data step.  Show what results you expect.

Did the code generation @Patrick posted not work for you?  Why are you trying to get SQL to generate the code instead?  Is the code you want to generate small enough to fit in a single macro variable?  A single macro variable on only hold 65K characters.

[Patrick]

Below creates sample data very close to what the Proc Import creates when run for the attached Excel sample data.

data LOOP_SAMPLE;
  FORMAT
    Item BEST12.
    Date DATE9.
    Loc  $CHAR4.
    Qty  BEST12.
    RLT  BEST12.;
  call streaminit(12345);
  do date='03oct2016'd to '12jan2020'd;
    do item=1 to 3;
      do loc='DDSP','DDCN','DDCT';
        if loc='DDSP' then rlt=20;
        else if loc='DDCN' then rlt=40;
        else rlt=20;
        qty=ceil(rand('uniform',0,999));
        output;
      end;
    end;
  end;
  stop;
run;