topic Re: Help with a do loop in Graphics Programming

Help with a do loop

malakaext — Fri, 14 Sep 2012 19:14:02 GMT

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

Re: Help with a do loop

GraphGuy — Fri, 14 Sep 2012 19:25:46 GMT

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;

Re: Help with a do loop

malakaext — Fri, 14 Sep 2012 19:30:02 GMT

Thank you very much!!!!

Re: Help with a do loop

malakaext — Fri, 14 Sep 2012 19:37:31 GMT

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;

Re: Help with a do loop

Haikuo — Fri, 14 Sep 2012 19:44:14 GMT

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;

AVG20=_total/20;

output;

call missing(_total);

keep date avg20;

run;

Haikuo

Re: Help with a do loop

Haikuo — Fri, 14 Sep 2012 19:58:35 GMT

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;

AVG20=mean(of temp(*));

output;

call missing(of temp(*));

keep date avg20;

run;

Haikuo

Re: Help with a do loop

malakaext — Fri, 14 Sep 2012 20:07:23 GMT

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

Re: Help with a do loop

Haikuo — Fri, 14 Sep 2012 20:17:58 GMT

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

Re: Help with a do loop

Haikuo — Fri, 14 Sep 2012 20:19:40 GMT

better yet, post first 10 results as well.

Haikuo

Re: Help with a do loop

malakaext — Fri, 14 Sep 2012 20:23:50 GMT

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

Re: Help with a do loop

Haikuo — Fri, 14 Sep 2012 21:03:37 GMT

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

Re: Help with a do loop

malakaext — Fri, 14 Sep 2012 21:13:57 GMT

Thank you very much!