I personally refuse to work with data in such a horrible (spreadsheet) format. I recommend you do that too. Apparently, @ballardw also recommends you do that.
I would transpose the data so that calendar information is now contained in a variable.
data a;
do i = 1 to 10;
reference_month = int(12*ranuni(123));
Jul21 = int(100*ranuni(123));
Aug21 = int(100*ranuni(123));
Sep21 = int(100*ranuni(123));
Oct21 = int(100*ranuni(123));
Nov21 = int(100*ranuni(123));
Dec21 = int(100*ranuni(123));
Jan22 = int(100*ranuni(123));
Feb22 = int(100*ranuni(123));
Mar22 = int(100*ranuni(123));
May22 = int(100*ranuni(123));
Apr22 = int(100*ranuni(123));
Jun22 = int(100*ranuni(123));
Jul22 = int(100*ranuni(123));
Aug22 = int(100*ranuni(123));
Sep22 = int(100*ranuni(123));
Oct22 = int(100*ranuni(123));
Nov22 = int(100*ranuni(123));
Dec22 = int(100*ranuni(123));
output;
end;
run;
proc transpose data=a out=b prefix=value;
by i reference_month;
var jul21--dec22;
run;
data c;
set b;
which_month=input(_name_,monyy.);
prev_col1=lag(value1);
prev_col2=lag2(value1);
prev_col3=lag3(value1);
prev_col4=lag4(value1);
prev_col5=lag5(value1);
prev_col6=lag6(value1);
if month(which_month)=reference_month then do;
score0=value1;
score1=prev_col1;
score2=prev_col2;
score3=prev_col3;
score4=prev_col4;
score5=prev_col5;
score6=prev_col6;
end;
if n(of score:)>0 then output;
format which_month monyy5.;
drop jul21--dec22 _name_ prev_:;
run;
Since your original data has Apr22 and May 22 in the wrong order, I haven't tried to fix that at all, but then of course the results have wrong ordering. I leave it as an assignment for you to fix.
Also, @znhnm , please help us (which helps you get faster and better answers) by always providing example data, as working SAS data step code. Don't wait for us to ask, just do it. Thank you.
... View more