CODEWITHSUNDEEP
sas
TTT
TTT

Reputation: 4434

Using macro and array to lag a group of variables in SAS

I tried to use macro and array to lag a bunch of variables by groups (id). my steps are: 1. lag all variables (groups not matter) 2. replace mis-lagged cells by .

but I found my code is buggy and appreciate any suggestions.

Here is the data:

data old;
  input id sale capx profit;
datalines;
1   11  111 1111
1   12  112 1112
1   13  113 1113
1   14  114 1114
1   15  115 1115
1   16  116 1116
1   17  117 1117
2   21  221 2221
2   22  222 2222
2   23  223 2223
3   31  331 3331
3   32  332 3332
3   33  333 3333
3   34  334 3334
4   41  441 4441
4   42  442 4442
4   43  443 4443
4   44  444 4444
4   45  445 4445
4   46  446 4446
;
run;

Code:

 data new;
 set old;
 run;

%macro lag_var(dataset, lag);
  proc sort data=&dataset;by id;
  data &dataset;
    set &dataset;
    by id;
    array vs(3) sale capx profit;
    %do j=1 %to 3;
            %do i=1 %to &lag;
            lag&j&i=lag&i(vs(&j));

            if first.id then 
            do;
            count=1;
            lag&j&i=.;
            end;
            count+1;
            if (not first.id and count<=&i) then
            do;
            lag&j&i=.;
            count+1;
            end;
            %end;
    %end;
  run;
%mend lag_var;

%lag_var(new,5)

Current output (wrong): enter image description here

My expected results: enter image description here

DomPazz's output: enter image description here

Upvotes: 1

Views: 2846

Answers (1)

DomPazz
DomPazz

Reputation: 12465

The reason those later groups are keeping their value is that you are over incrementing count.

I think this does what you are looking for:

%macro lag_var(dataset, lag);
  proc sort data=&dataset;by id;
  data &dataset;
    set &dataset;
    by id;
    array vs(3) sale capx profit;
    %do j=1 %to 3;
            %do i=1 %to &lag;
            lag&j&i=lag&i(vs(&j));
            %end;
    %end;

    if first.id then do;
       count=0;
       %do j=1 %to 3;
          %do i=1 %to &lag;
             lag&j&i=.;
          %end;
       %end;
    end;

    count+1;

    %do j=1 %to 3;
       %do i=1 %to &lag;

          if (not first.id and count<=&i) then do;
               lag&j&i=.;
            /*count+1;*/
          end;
       %end;
    %end;
  run;
%mend lag_var;

EDIT: Changed the initialization of count from 1 to 0.

Upvotes: 2

Related Questions