Hi guys,
I have a dataset, and i calculated the sharpe ratio for 1 year. But now I also want it for 3 and 5 years. I should be something like this for 3 years:
if YEAR = 1990 THEN PERIOD_SLABBINCK = '1990 - 1992';
if YEAR = 1991 THEN PERIOD_SLABBINCK = '1990 - 1992';
if YEAR = 1992 THEN PERIOD_SLABBINCK = '1990 - 1992';
if YEAR = 1993 THEN PERIOD_SLABBINCK = '1993 - 1995';
if YEAR = 1994 THEN PERIOD_SLABBINCK = '1993 - 1995';
if YEAR = 1195 THEN PERIOD_SLABBINCK = '1993 - 1995';
if YEAR = 1996 THEN PERIOD_SLABBINCK = '1996 - 1998';
if YEAR = 1997 THEN PERIOD_SLABBINCK = '1996 - 1998';
if YEAR = 1998 THEN PERIOD_SLABBINCK = '1996 - 1998';
if YEAR = 1999 THEN PERIOD_SLABBINCK = '1999 - 2001';
if YEAR = 2000 THEN PERIOD_SLABBINCK = '1999 - 2001';
if YEAR = 2001 THEN PERIOD_SLABBINCK = '1999 - 2001';
if YEAR = 2002 THEN PERIOD_SLABBINCK = '2002 - 2004';
if YEAR = 2003 THEN PERIOD_SLABBINCK = '2002 - 2004';
if YEAR = 2004 THEN PERIOD_SLABBINCK = '2002 - 2004';
if YEAR = 2005 THEN PERIOD_SLABBINCK = '2005 - 2007';
if YEAR = 2006 THEN PERIOD_SLABBINCK = '2005 - 2007';
if YEAR = 2007 THEN PERIOD_SLABBINCK = '2005 - 2007';
if YEAR = 2008 THEN PERIOD_SLABBINCK = '2008 - 2011';
if YEAR = 2009 THEN PERIOD_SLABBINCK = '2008 - 2011';
if YEAR = 2010 THEN PERIOD_SLABBINCK = '2008 - 2011';
if YEAR = 2011 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2012 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2013 THEN PERIOD_SLABBINCK = '2009 - 2013';
And for 5 years:
if YEAR = 1989 THEN PERIOD_SLABBINCK = '1989 - 1993';
if YEAR = 1990 THEN PERIOD_SLABBINCK = '1989 - 1993';
if YEAR = 1991 THEN PERIOD_SLABBINCK = '1989 - 1993';
if YEAR = 1992 THEN PERIOD_SLABBINCK = '1989 - 1993';
if YEAR = 1993 THEN PERIOD_SLABBINCK = '1989 - 1993';
if YEAR = 1994 THEN PERIOD_SLABBINCK = '1994 - 1998';
if YEAR = 1195 THEN PERIOD_SLABBINCK = '1994 - 1998';
if YEAR = 1996 THEN PERIOD_SLABBINCK = '1994 - 1998';
if YEAR = 1997 THEN PERIOD_SLABBINCK = '1994 - 1998';
if YEAR = 1998 THEN PERIOD_SLABBINCK = '1994 - 1998';
if YEAR = 1999 THEN PERIOD_SLABBINCK = '1999 - 2003';
if YEAR = 2000 THEN PERIOD_SLABBINCK = '1999 - 2003';
if YEAR = 2001 THEN PERIOD_SLABBINCK = '1999 - 2003';
if YEAR = 2002 THEN PERIOD_SLABBINCK = '1999 - 2003';
if YEAR = 2003 THEN PERIOD_SLABBINCK = '1999 - 2003';
if YEAR = 2004 THEN PERIOD_SLABBINCK = '2004 - 2008';
if YEAR = 2005 THEN PERIOD_SLABBINCK = '2004 - 2008';
if YEAR = 2006 THEN PERIOD_SLABBINCK = '2004 - 2008';
if YEAR = 2007 THEN PERIOD_SLABBINCK = '2004 - 2008';
if YEAR = 2008 THEN PERIOD_SLABBINCK = '2004 - 2008';
if YEAR = 2009 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2010 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2011 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2012 THEN PERIOD_SLABBINCK = '2009 - 2013';
if YEAR = 2013 THEN PERIOD_SLABBINCK = '2009 - 2013';
So this are the subperiods I want.
My code for the 1 year calculation is this:
proc sql;
create table start as
select *,ret_fund - market as excess_return,year(date) as year
from cbhk_lgd.end_dataset
order by isin,date;
quit;
proc sql;
create table avg_excess_return as
select isin,jaar,avg(excess_return) as avg_excess_return,std(excess_return) asstd_excess_return,avg(excess_return)/std(excess_return) as sharpe
from start
group by isin,year;
quit;
So my question is how do I calculate the above formula for 3 and 5 year subperiods?
Thanks in advance!
Are you talking about CV (coefficient of variation )?
proc means data=have cv ;
by isin PERIOD_SLABBINCK;
var excess_return;
run;
Xia Keshan
Why not use MLF multi-label formats with the formats being the recoding in 3 and 5 year periods?
Use the SAS procs supporting that. Advantage: No need for data-transformations.
Hi Jaap,
How do you use those multi-label formats?
I am rather a noob in SAS, my apologies
So what would the code be like?
There are samples like Base SAS(R) 9.2 Procedures Guide ( Example 4: Using Multilabel Formats ) This one based on ages where you are needed dates, the concept are similar.
Formats are a unique feature in SAS that some others (Excel Oracle) have copied. http://support.sas.com/resources/papers/proceedings11/271-2011.pdf
You do not change the data but you modify the way it is presented, there is no data change done. That is different to SQL as you would think in creating other datasets first.
The advantage of formats no additional creating of datasets needed.
The analytic processing with SAS-procs is normally supporting the formatted value. Some SAS Procs are supporting multi-label.
I tried to fit the code into my original code, but it doesn't work...
How do I fit in the proc format into my code?
proc sql;
create table start as
select *,ret_fund - market as excess_return,year(date) as year
from cbhk_lgd.end_dataset
order by isin,date;
quit;
proc sql;
create table avg_excess_return as
select isin,jaar,avg(excess_return) as avg_excess_return,std(excess_return)asstd_excess_return,avg(excess_return)/std(excess_return) as sharpe
from start
group by isin,year;
quit;
As start defining the formats ( I am not able to check as no access to SAS at the moment ).
it is converting dates to strings. SAS(R) 9.2 Language Reference: Concepts, Second Edition (date constants).
With a put function you can use it within SQL for the conversion like: SAS(R) 9.3 In-Database Products: User's Guide, Fourth Edition
The yearf3. and yearf5. formats can be used in that way.
Using proc tabulate/report you can use a lot of statistics direct to a report. Base SAS(R) 9.2 Procedures Guide (proc tabulate)
mean (avg) stddev are basic ones. You need to work on your data using the samples and see what happens.
The yearfmt. MLF can be used with these procedures. Immediate giving the report.
proc format;
value yearfmt (multilabel notsorted)
'01jan1990'd - '31dec1992'd = '1990 - 1992'
'01jan1993'd - '31dec1994'd = '1993 - 1995'
'01jan1996'd - '31dec1998'd = '1996 - 1998'
'01jan1999'd - '31dec2001'd = '1999 - 2001'
'01jan2002'd - '31dec2004'd = '2002 - 2004'
'01jan2005'd - '31dec2007'd = '2005 - 2007'
'01jan2008'd - '31dec2011'd = '2008 - 2011'
'01jan2009'd - '31dec2013'd = '2009 - 2013'
'01jan1989'd - '31dec1993'd = '1989 - 1993'
'01jan1994'd - '31dec1998'd = '1994 - 1998'
'01jan1999'd - '31dec2003'd = '1999 - 2003'
'01jan2004'd - '31dec2008'd = '2004 - 2008'
'01jan2009'd - '31dec2013'd = '2009 - 2013'
;
value yearfy3
'01jan1990'd - '31dec1992'd = '1990 - 1992'
'01jan1993'd - '31dec1994'd = '1993 - 1995'
'01jan1996'd - '31dec1998'd = '1996 - 1998'
'01jan1999'd - '31dec2001'd = '1999 - 2001'
'01jan2002'd - '31dec2004'd = '2002 - 2004'
'01jan2005'd - '31dec2007'd = '2005 - 2007'
'01jan2008'd - '31dec2011'd = '2008 - 2011'
'01jan2009'd - '31dec2013'd = '2009 - 2013'
;
value yearfy5
'01jan1989'd - '31dec1993'd = '1989 - 1993'
'01jan1994'd - '31dec1998'd = '1994 - 1998'
'01jan1999'd - '31dec2003'd = '1999 - 2003'
'01jan2004'd - '31dec2008'd = '2004 - 2008'
'01jan2009'd - '31dec2013'd = '2009 - 2013'
;
run;
If if put this:
proc format;
value yearfy3
'01jan1990'd - '31dec1992'd = '1990 - 1992'
'01jan1993'd - '31dec1994'd = '1993 - 1995'
'01jan1996'd - '31dec1998'd = '1996 - 1998'
'01jan1999'd - '31dec2001'd = '1999 - 2001'
'01jan2002'd - '31dec2004'd = '2002 - 2004'
'01jan2005'd - '31dec2007'd = '2005 - 2007'
'01jan2008'd - '31dec2011'd = '2008 - 2011'
'01jan2009'd - '31dec2013'd = '2009 - 2013'
;
run;
proc sql;
create table start as
select *,ret_fund - market as excess_return,year(date) as year
from cbhk_lgd.end_dataset
order by isin,date;
quit;
proc sql;
create table avg_excess_return as
select isin,jaar,avg(excess_return) asavg_excess_return,std(excess_return)asstd_excess_return,avg(excess_return)/std(excess_return) as sharpe
from start
group by isin,year;
quit;
Nothing happens...
Ok, the first time you are seeing the format usage. Did the proc format ended well?
I will try (blind coding no validation) the first SQL.
proc sql;
create table start as
select *,ret_fund - market as excess_return, put(date,yearfy3.) as year
from cbhk_lgd.end_dataset
order by isin,date;
quit;
Thanks a lot, that worked!
I am not such a great talent in SAS
Hi Jaap,
It worked, but I think there is still a mistake in it.
If I put the code like that, than I still get the sharpe ratio of each fund per day. The only new thing is that there is a column 'year' with the correct years.
So nothing changed :smileysilly:
Bjinge, I only checked adviced on the grouping of years. Ok, that is working.
What is Sharpe ratio calculation and how did you do that? Sharpe ratio - Wikipedia, the free encyclopedia
select isin,jaar,avg(excess_return) as avg_excess_return, std(excess_return)as std_excess_return, avg(excess_return)/std(excess_return) as sharpe
Nice ... uhhhhh you are using jaar? That is Dutch. Should it not be year?
Are we doing some act? (De lamme en de blinde) You are having access to SAS Needin Knowledge in using, I have no access to SAS knowing how to use.
The sharp ratio is expecting some intervals. As the original data is not changed having the same number of records the calculations are based on those detailed-records.
Is this what you want?
The calculations of the sharpe ratio is correct, I checked that before.
And indeed that should have been year, but I changed that in the original code, but nothing new happened.
"The sharp ratio is expecting some intervals. As the original data is not changed having the same number of records the calculations are based on those detailed-records.
Is this what you want? "
This is exactly what I am looking for...
And about the 'lamme en de blinde', I think I am both of them
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 25. Read more here about why you should contribute and what is in it for you!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.