CODEWITHSUNDEEP
sassas-macro
Wioleta Zgliczyńska
Wioleta Zgliczyńska

Reputation: 57

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

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
;

Upvotes: 0

Views: 724

Answers (2)

Tom
Tom

Reputation: 51601

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.

Upvotes: 1

Richard
Richard

Reputation: 27508

The LAG<n> function is an in-place stack of fixed depth that is specific to it's code use location and thus step state at invocation. The stack is of depth and can not be altered dynamically at runtime.

A dynamic lag can be implemented in SAS DATA step using a hash object. The double DOW technique allows a group to be measured and then subsequently it's items operated upon.

Sample code

This example uses a defines a hash object that maintains a stack of values within a group. A first DOW loop computes the maximum of a field that becomes the dynamic stack height. The second DOW loop iterates of the group and retrieves the lag value while also building up the stack for future item lags.

* some faux data;

data have (keep=group value duration);
  do group = 1 to 10;
    limit = ceil(4 * ranuni(6));
    put group= limit=;
    do _n_ = 1 to 8 + 10*ranuni(123);
      value = group*10 + _n_;
      duration = 1 + floor(limit*ranuni(123));
      output;
    end;
  end;
run;

* dynamic lag provided via hash;

data want;
  if _n_ = 1 then do;
    retain index lag_value .;
    declare hash lag_stack();
    lag_stack.defineKey('index');
    lag_stack.defineData('lag_value');
    lag_stack.defineDone();
  end;

  do _n_ = 1 by 1 until (last.group);
    set have;
    by group;
    max_duration = max(max_duration, duration);
  end;

  * max_duration within group is the lag lag_stack height;

  * pre-fill missings ;
  do index = 1-max_duration to 0;
    lag_stack.replace(key: index, data: .);
  end;

  do _n_ = 1 to _n_;
    set have;
    lag_stack.replace(key: _n_, data: value);
    lag_stack.find(key: _n_ - max_duration);
    output;
  end;

  drop index;
run;

Another technique would involve a fixed length ring-array instead of a hash-stack, but you would need to compute the maximum lag over all groups prior to coding the DATA step using the array.

Upvotes: 0

Related Questions