Dynamic n in function LAG<n> (variable) SAS_part2

Question

do you know how to use n in function LAGn(variable) that refer to another macro variable in the program-> max in my case by V1?

   data example1;
input V1 value V2;
datalines;
a 1.0 2.0
a 1.0 1.0
a 1.0 1.0
b 1.0 1.0
b 1.0 1.0
;       

proc sql;
  select max(V2) format = 1. into :n
  from example1;
quit;

data example1;
  set example1;
  by V1;
  lagval=lag&n(V2);
  run;

Code from user667489 and works for one column. Now n changes by V1. I expect:

          MAX LAG
a 1.0 2.0  2  .
a 1.0 1.0  2  .
a 1.0 1.0  2  2
b 1.0 1.0  1  .
b 1.0 1.0  1  1
;    

Answer 1

Forget about LAG(). Just add a counter variable and join on that.

Let's fix your example data step so it works.

data example1;
  input V1 $ value V2;
datalines;
a 1 2
a 1 1
a 1 1
b 1 1
b 1 1
;

Now add a unique row id within each BY group.

data step1;
  set example1;
  by v1;
  if first.v1 then row=0;
  row+1;
run;

Now just join this dataset with itself.

proc sql ;
 create table want as
   select a.*,b.v2 as lag_v2
   from (select *,max(v2) as max_v2 from step1 group by v1) a
   left join step1 b
   on a.v1= b.v1 and a.row = b.row + a.max_v2
 ;
quit;

Results:

Obs    V1    value    V2    row    max_v2    lag_v2

 1     a       1       2     1        2         .
 2     a       1       1     2        2         .
 3     a       1       1     3        2         2
 4     b       1       1     1        1         .
 5     b       1       1     2        1         1

Hopefully your real use case makes more sense than than this example.