topic Re: rolling window in Statistical Procedures

rolling window

sasphd — Tue, 18 Nov 2014 16:57:19 GMT

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;

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?

Re: rolling window

art297 — Tue, 18 Nov 2014 17:33:17 GMT

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;

Re: rolling window

sasphd — Tue, 18 Nov 2014 17:44:10 GMT

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

Re: rolling window

art297 — Tue, 18 Nov 2014 18:30:24 GMT

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;

run;

Re: rolling window

sasphd — Tue, 18 Nov 2014 19:56:37 GMT

thanks a lot

Re: rolling window

Ksharp — Wed, 19 Nov 2014 13:43:57 GMT

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

Re: rolling window

art297 — Wed, 19 Nov 2014 14:53:46 GMT

: 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.

Re: rolling window

sasphd — Wed, 19 Nov 2014 14:58:41 GMT

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.

Re: rolling window

sasphd — Wed, 19 Nov 2014 15:00:27 GMT

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.

Re: rolling window

art297 — Wed, 19 Nov 2014 15:20:30 GMT

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.

Re: rolling window

sasphd — Wed, 19 Nov 2014 15:28:29 GMT

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.

Re: rolling window

art297 — Wed, 19 Nov 2014 15:38:46 GMT

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.

Re: rolling window

art297 — Wed, 19 Nov 2014 15:58:24 GMT

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.

Re: rolling window

Ksharp — Thu, 20 Nov 2014 12:28:12 GMT

En. Interesting. Good to know.