BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
malakaext
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
GraphGuy
Meteorite | Level 14

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;

View solution in original post

11 REPLIES 11
GraphGuy
Meteorite | Level 14

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
Calcite | Level 5

Thank you very much!!!!

malakaext
Calcite | Level 5

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
Onyx | Level 15

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
Onyx | Level 15

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
Calcite | Level 5

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
Onyx | Level 15

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
Onyx | Level 15

better yet, post first 10 results as well.

Haikuo

malakaext
Calcite | Level 5

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
Onyx | Level 15

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

101/29/1990-.002211198
202/26/1990-.001736703
303/26/19900.001075358
404/25/1990-.003493642
505/24/19900.003032466
606/22/19900.002178367
707/20/19900.000452018
808/17/1990-.004880428
909/17/1990-.001934124
1010/15/1990

0.000181116

Haikuo

malakaext
Calcite | Level 5

Thank you very much!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1864 views
  • 6 likes
  • 3 in conversation