rolling window

[sasphd]

Hello,

I try to do a rolling window. I have this program.

data rolling(keep=grp id a b) / view=rolling;

do grp = 0 to nbid-9;

    do j = 1 + grp to 9 + grp;

          set test nobs=nbid point=j;

          output;

          end;

    end;

stop;

run;

My data have the form

id	a	b
1	1	8,00
1	2	6,00
1	3	5,00
1	4	3,33
1	5	1,83
1	6	0,33
1	7	-1,17
1	8	-2,67
1	9	-4,17
1	10	-5,67

I want to create a rolling window with 9 obsevations by id. Result

id	a	b
1	1	8,00
1	2	6,00
1	3	5,00
1	4	3,33
1	5	1,83
1	6	0,33
1	7	-1,17
1	8	-2,67
1	9	-4,17
1	2	6,00
1	3	5,00
1	4	3,33
1	5	1,83
1	6	0,33
1	7	-1,17
1	8	-2,67
1	9	-4,17
1	10	-5,67

In attachement you can find the data.

My program do not work well with different id. It work well when I have just one id.

I need help?

[art297]

If you don't care about the order of the records within each set of nine records then you could just use something like:

data want (keep=id a b);

  set have (rename=(a=_a b=_b));

  by id;

  retain counter .;

  array stacka {0:8};

  array stackb {0:8};

  retain stacka stackb;

  if first.id then do;

    call missing(of stacka{*});

    call missing(of stackb{*});

    counter=0;

  end;

  counter+1;

  stacka{mod(counter,9)} = _a;

  stackb{mod(counter,9)} = _b;

  if counter gt 8 then do i=0 to 8;

    a=stacka{i};

    b=stackb{i};

    output;

  end;

run;

[sasphd]

But the order of observations is very important for me?????????????

[art297]

Then how about something like?:

data rolling(keep=grp id a b);

  set test (keep=id);

  by id;

  if first.id then do;

    grp=0;

    counter=1;

  end;

  else counter+1;

  if counter ge 9 then do;

    grp+1;

    do i=8 to 0 by -1;

      pointer=_n_-i;

      set test point=pointer;

      output;

    end;

  end;

run;

[sasphd]

thanks a lot

[Ksharp]

Base on Arthur.T's code and idea . If you have a big table.

data have;
input id     a     b : commax.;
cards;
1     1     8,00
1     2     6,00
1     3     5,00
1     4     3,33
1     5     1,83
1     6     0,33
1     7     -1,17
1     8     -2,67
1     9     -4,17
1     10     -5,67
;
run;


data want ;
  set have ;
  by id;
  array stacka {9} _temporary_;
  array stackb {9} _temporary_;
  if first.id then do;
    call missing(of stacka{*} stackb{*});
     counter=0;
  end;
  counter+1;
do i=2 to dim(stacka);
   stacka{i-1}=stacka{i};
   stackb{i-1}=stackb{i};
end;
stacka{9}=a; stackb{9}=b; 
if counter gt 8 then do i=1 to 9;
    a=stacka{i};
    b=stackb{i};
    output;
  end;
drop i counter;
run;

Xia Keshan

[art297]

: I, too, would have thought that doing everything via arrays would be faster than re-reading the data, on my computer (at least) the version using point runs 50% faster than reshuffling the arrays. I ran my test by increasing the original dataset to represent 4 million records.

[sasphd]

However I have a lot of variables in my table not only a and b. So writing the program of xia will be long also.

[sasphd]

can you please help me in doing this directly in proc model to save time ??

I am working on a research project and have trouble come up with a way to conduct a rolling window regression. In my proc model I include this macro to do my rolling window.

%do fen = %eval(&dfen) %to 100;

where %eval(&fen) le obs le %eval(36+&fen-1);

However, my proc model is done by id and as you can see in this macro I suppose that the number of observations is 100 for each id but this is not true I have a different number of observation for each id. In my database I add a column which indicates the number of total number of observation for each id (nb).

How can I change the fixed number 100 to a changing variables called nb?

I try to do it like this :

%do fen = %eval(&dfen) %to %eval(&nb);

where %eval(&fen) le obs le %eval(36+&fen-1);

but I have this error

ARNING: Apparent symbolic reference NB not resolved.

ERROR: The text expression &NB contains a recursive reference to the macro variable NB.  The

       macro variable will be assigned the null value.

[art297]

I suggest dropping the macro, use the point version that I suggested, and use by group processing on the new file.

You can probably use just one call to the proc if you include: by id grp;

However, without seeing your actual code, it's difficult to know if that would work.

[sasphd]

thanks.

I have generated the file with rolling window using your program. After that when I run my program (proc model) using by id grp, SAS makes the error message of unsufficient memory because my file is very huge like 17214480 lines.

That is why I want to use macro.

[art297]

Post your code.  My guess is that you are using both by and class statements, and that the class statement is causing the memory issue.

[art297]

If you don't get a response here I suggest posting the code and issue in the Statistical Procedure's forum. I'm not sufficiently familiar with Proc Model.

[Ksharp]

En. Interesting. Good to know.