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
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;
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;
Thank you very much!!!!
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;
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
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
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
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
better yet, post first 10 results as well.
Haikuo
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
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
Thank you very much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: