topic Re: SAS 1year to 3/5 years in Statistical Procedures

SAS 1year to 3/5 years

bjinge — Sat, 10 May 2014 10:41:24 GMT

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!

Re: SAS 1year to 3/5 years

Ksharp — Sat, 10 May 2014 13:42:48 GMT

Are you talking about CV (coefficient of variation )?

proc means data=have cv ;

by isin PERIOD_SLABBINCK;

var excess_return;

run;

Xia Keshan

Re: SAS 1year to 3/5 years

jakarman — Sat, 10 May 2014 15:49:56 GMT

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.

Re: SAS 1year to 3/5 years

bjinge — Sun, 11 May 2014 09:38:43 GMT

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?

Re: SAS 1year to 3/5 years

jakarman — Sun, 11 May 2014 15:00:02 GMT

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.

Re: SAS 1year to 3/5 years

bjinge — Mon, 12 May 2014 12:41:14 GMT

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;

Re: SAS 1year to 3/5 years

jakarman — Mon, 12 May 2014 14:07:58 GMT

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;

Re: SAS 1year to 3/5 years

bjinge — Mon, 12 May 2014 15:27:48 GMT

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

Re: SAS 1year to 3/5 years

jakarman — Mon, 12 May 2014 15:39:30 GMT

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;

Re: SAS 1year to 3/5 years

bjinge — Tue, 13 May 2014 15:51:33 GMT

Thanks a lot, that worked!

I am not such a great talent in SAS

Re: SAS 1year to 3/5 years

bjinge — Tue, 13 May 2014 16:20:20 GMT

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:

Re: SAS 1year to 3/5 years

jakarman — Tue, 13 May 2014 17:18:35 GMT

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?

Re: SAS 1year to 3/5 years

bjinge — Wed, 14 May 2014 07:06:52 GMT

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