Programming the statistical procedures from SAS

SAS 1year to 3/5 years

Reply
Occasional Contributor
Posts: 12

SAS 1year to 3/5 years

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!


Super User
Posts: 9,769

Re: SAS 1year to 3/5 years

Are you talking about CV (coefficient of variation )?

proc means data=have  cv ;

by isin PERIOD_SLABBINCK;

var excess_return;

run;

Xia Keshan

Valued Guide
Posts: 3,208

Re: SAS 1year to 3/5 years

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 --<-----
Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

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?

Valued Guide
Posts: 3,208

Re: SAS 1year to 3/5 years

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 --<-----
Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

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;


Valued Guide
Posts: 3,208

Re: SAS 1year to 3/5 years

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 --<-----
Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

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

Valued Guide
Posts: 3,208

Re: SAS  1year to 3/5 years

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 --<-----
Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

Thanks a lot, that worked!

I am not such a great talent in SAS Smiley Happy

Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

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:

Valued Guide
Posts: 3,208

Re: SAS 1year to 3/5 years

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 --<-----
Occasional Contributor
Posts: 12

Re: SAS 1year to 3/5 years

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

Ask a Question
Discussion stats
  • 12 replies
  • 432 views
  • 0 likes
  • 3 in conversation