BookmarkSubscribeRSS Feed
sasphd
Lapis Lazuli | Level 10

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

idab
118,00
126,00
135,00
143,33
151,83
160,33
17-1,17
18-2,67
19-4,17
110-5,67

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

idab
118,00
126,00
135,00
143,33
151,83
160,33
17-1,17
18-2,67
19-4,17
126,00
135,00
143,33
151,83
160,33
17-1,17
18-2,67
19-4,17
110-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?

13 REPLIES 13
art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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
Lapis Lazuli | Level 10

thanks a lot

Ksharp
Super User

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
Opal | Level 21

: 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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Opal | Level 21

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
Super User

En. Interesting. Good to know.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 2143 views
  • 0 likes
  • 3 in conversation