Data visualization with SAS programming

Help with a do loop

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Help with a do loop

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

Attachment

Accepted Solutions
Solution
‎09-14-2012 03:25 PM
SAS Employee
Posts: 980

Re: Help with a do loop

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


All Replies
Solution
‎09-14-2012 03:25 PM
SAS Employee
Posts: 980

Re: Help with a do loop

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;

Frequent Contributor
Posts: 91

Re: Help with a do loop

Thank you very much!!!!

Frequent Contributor
Posts: 91

Re: Help with a do loop

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;

Respected Advisor
Posts: 3,156

Re: Help with a do loop

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

Respected Advisor
Posts: 3,156

Re: Help with a do loop

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

Frequent Contributor
Posts: 91

Re: Help with a do loop

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

Respected Advisor
Posts: 3,156

Re: Help with a do loop

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

Respected Advisor
Posts: 3,156

Re: Help with a do loop

better yet, post first 10 results as well.

Haikuo

Frequent Contributor
Posts: 91

Re: Help with a do loop

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

Respected Advisor
Posts: 3,156

Re: Help with a do loop

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

Frequent Contributor
Posts: 91

Re: Help with a do loop

Thank you very much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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