Thanks to both of you!!!!!! I believe either solution will work. I will try and integrate.
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.
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;
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.