Help with a do loop

[malakaext]

I have a data set for daily returns. (Attached)

I want to make a new data set such that I get the average returns for periods of 20 days.

For instance if I have 100 daily returns, the new data set should have five data points for returns..

Can you please help me with this code.

Thanks

[GraphGuy · Accepted solution]

Hmm ... this is not a very "graphical" thing, but I think this will do what you want...

PROC IMPORT OUT=foo

            DATAFILE="ftse.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

data foo; set foo;

period=int((_n_-1)/20)+1;

run;

proc sql;

create table foo2 as

select unique period, avg(return) as avg_return

from foo

group by period;

quit; run;

[malakaext]

Thank you very much!!!!

[malakaext]

Why does it give the following error:

303

304  proc sql;

305  create table foo2 as

306  select unique period, avg(return) as avg_return

307  from foo

308  group by period;

ERROR: The AVG summary function requires a numeric argument.

ERROR: The following columns were not found in the contributing tables: return.

309  quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

310  run;

[Haikuo]

You are right that your task can require a loop, please modify the file location to suit your settiings.

filename in 'h:\temp\ftse.csv';

data want;

  do _n_=1 to 20;

   infile in firstobs=2 truncover dsd end=last;

   input  date :mmddyy10. return;

   format date mmddyy10.;

   _total+return;

   if last then do;

      AVG20=_total/_n_;

        output;

        stop;

      end;

   end;

   AVG20=_total/20;

   output;

   call missing(_total);

   keep date avg20;

  run;

Haikuo

[Haikuo]

Similarily you could involve array():

data want1;

array temp(1:20) _temporary_;

  do _n_=1 to 20;

   infile in firstobs=2 truncover dsd end=last;

   input  date :mmddyy10. return;

   format date mmddyy10.;

   temp(_n_)=return;

   if last then do;

     AVG20=mean(of temp(*));

        output;

        stop;

      end;

   end;

   AVG20=mean(of temp(*));

   output;

   call missing(of temp(*));

   keep date avg20;

  run;

Haikuo

[malakaext]

I need the average of the returns per 20 day period. but when I printed the data set "want" those values were not the averages of the returns, but very large values.

Is there any way to correct it. Im referring to your first code.

Thanks

[Haikuo]

That is rather odd. Both of my code will have the identical result, and from my validation point, they are what you want.

Mind posting the actuall code you used?

Haikuo

[Haikuo]

better yet, post first 10 results as well.

Haikuo

[malakaext]

I used the exact code you sent me..

                                       1    01/30/1990    2256.01

                                       2    02/27/1990    2191.34

                                       3    03/27/1990    2138.42

                                       4    04/26/1990    2110.02

                                       5    05/25/1990    2096.36

                                       6    06/25/1990    2251.66

                                       7    07/23/1990    2259.39

                                       8    08/20/1990    2169.69

                                       9    09/18/1990    2027.32

                                      10    10/16/1990    1972.80

                                      11    11/13/1990    1966.80

But the average for the first 20 returns(say) should be -0.002211

Malaka

[Haikuo]

This is what I got, I have no idea what is going on with your end. Are we using the same raw data?

1	01/29/1990	-.002211198
2	02/26/1990	-.001736703
3	03/26/1990	0.001075358
4	04/25/1990	-.003493642
5	05/24/1990	0.003032466
6	06/22/1990	0.002178367
7	07/20/1990	0.000452018
8	08/17/1990	-.004880428
9	09/17/1990	-.001934124
10	10/15/1990	0.000181116

Haikuo

[malakaext]

Thank you very much!