BookmarkSubscribeRSS Feed
bjinge
Calcite | Level 5

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!


12 REPLIES 12
Ksharp
Super User

Are you talking about CV (coefficient of variation )?

proc means data=have  cv ;

by isin PERIOD_SLABBINCK;

var excess_return;

run;

Xia Keshan

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
bjinge
Calcite | Level 5

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?

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
bjinge
Calcite | Level 5

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;


jakarman
Barite | Level 11

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;                

---->-- ja karman --<-----
bjinge
Calcite | Level 5

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...

jakarman
Barite | Level 11

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;

---->-- ja karman --<-----
bjinge
Calcite | Level 5

Thanks a lot, that worked!

I am not such a great talent in SAS Smiley Happy

bjinge
Calcite | Level 5

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:

jakarman
Barite | Level 11

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?

---->-- ja karman --<-----
bjinge
Calcite | Level 5

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 Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

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.

Discussion stats
  • 12 replies
  • 2124 views
  • 0 likes
  • 3 in conversation