BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5

Hello again,

I have daily stock prices and I need to calculate the monthly volatility. I attach a sample file that describes the process in excel, though since there are too many stocks working in excel is too much time concuming.

Is there a way to do that in SAS having all stocks in one file?

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Yes, there were definitely some things that had to be changed in order to match the results.  I think that the following calculates exactly what you were doing in Excel.  The main differences from what I had originally proposed are: (1) using the log10 function rather than the log function; (2) multiplying the differences by 100; and (3) using the corrected rather than the uncorrected sums of squares.

data need;

  set have;

  array vars(*) a1--a100;

  array diff(100);

  yrmon=put(DATE,yymmd.);

  do i=1 to dim(vars);

    diff(i)=(log10(vars(i))-log10(lag(vars(i))))*100;

  end;

run;

proc means data=need mean uss nway noprint;

  var diff1--diff100;

  class yrmon;

  output out=want (drop=_:)

   mean= css= /autoname;

run;

data want;

  set want;

  array diffs(*) diff1_CSS--diff100_CSS;

  do i=1 to dim(diffs);

    if diffs(i) gt 0 then

    diffs(i)=diffs(i)**0.5;

  end;

run;

View solution in original post

18 REPLIES 18
art297
Opal | Level 21

Should be easy, but one would have to know what you would want or need in the output file.  You are only creating a new variable, namely the log of the current price minus the log of the previous price and then calculated the sum of squares (USS) for those values.  That could be as simple as a datastep (creating a month and year variables from date and creating a normalized price change variable (i.e., log(price)-log(lag(price))) and a proc means with USS as the desired stat and using year and month as class variables.

Costasg
Calcite | Level 5

Hi art297,

thanks for your reply. It should be easy, though I am new to SAS and I do not have sufficient knowledge on it. So far I have a table with the stocks' log diferrences (100 stocks) and another table with the monthly averages of them.

What I want to do is to create a new table where the variables will be the log price minus the monthly average. Keep in mind tha the log prices are daily and the average is for each month, so all prices of january 2000 will be minus the average of this month, the prices of february 2000 with the average of this month and so on. Below is how the data looks like:

My log prices table:

DATEA1A2A100
31/1/20000.2560.54240.564
1/2/20000.3210.12230.446
2/2/20000.4530.415540.265
3/2/20000.15240.1510.456
4/2/20000.20450.16560.355

My average table has prices of the form:

DATEA1A2A100
JAN20000.1520.1560.256
FEB20000.2350.2450.3565
MAR20000.4560.6540.265

What I want to get is :

DATEA1A2A100
31/1/20000.256-0.1520.5424-0.1560.564-0.256
1/2/20000.321-0.2350.151-0.2450.456-0.3565
2/2/20000.453-0.2350.1656-0.2450.355-0.3565

(numbers only for reference)

So each price will be substracted by the given average of the specific month.

Any suggestions?

art297
Opal | Level 21

It would be easy to generate your desired file, but what I was mentioning is that the file wouldn't even be needed to achieve the final result I think you want.

You could simply take the first file and run something like:

data have;

  informat date ddmmyy10.;

  input DATE A1 A2 A100;

  yrmon=put(DATE,yymmd.);

  cards;

31/1/2000 0.256 0.5424 0.564

1/2/2000 0.321 0.1223 0.446

2/2/2000 0.453 0.41554 0.265

3/2/2000 0.1524 0.151 0.456

4/2/2000 0.2045 0.1656 0.355

;

proc means data=have mean uss;

  var a1--a100;

  class yrmon;

run;

You can also add an output statement with that same proc and produce a file that contains the calculations which could then be merged with your input data file.  But, if all you really want is the result, the above proc means will calculate your desired numbers.

Costasg
Calcite | Level 5

Sorry for bothering you again but I am really newbie into SAS and I don't understand how some things work. I imported my data from an excel file which has 100 stocks and some thousands of observations. Normally I should skip the cards part that u have written.

I am a bit confused with the yrmon varibale

since the data is imported through the manager and already exists as a table, If I write

data have;

input DATE A1-A100;

proc means data=have mean uss;

var a1--a100;

run;

I get the overall USS for each variable, not the monthly,

I tried with yrmon=put(DATE,yymmd.); after the input statement and later in the proc means statement as u wrote but it doesn't work.

How I could modified this in order to make it work?

I just want to produce a file I can export with the desired results.

Sorry If I make any silly questions but as I said I am really new into SAS.

thanks for your understanding

art297
Opal | Level 21

You said that you imported the file from excel.  In the resulting SAS file, what kind of field is the field "DATE" (i.e., character or numeric), and what does at least one of the entries look like?

Costasg
Calcite | Level 5

The date has the form of   21JUN1996 (format: DATE9.)

and the entries have the form 1.0234345595 (fomat: BEST12.)

art297
Opal | Level 21

If it is really a numeric field with a date9. format, the code I originally suggested would have worked.  If the file were called have, you would have been able to submit the following:

data need;

   set have;

  yrmon=put(DATE,yymmd.);

run;

proc means data=need mean uss;

  var a1--a100;

  class yrmon;

run;

Run the following on your file to find out if the field is really character or numeric:

proc contents data=have;

run;

Art

Costasg
Calcite | Level 5

It works!!!

The only problem now is than in my output table  (the one I want to export) there is no USS, only min, max, mean, std and N. How can i fix this?

Thanks

Costasg
Calcite | Level 5

Something else, I compared the calculations from SAS with those that I did manually for some stocks and they have totally different results. Maybe the model I use measure s differently the volatility.

So, if I want to to do what I said above, I mean from the daily prices of each month substract the average of the specific month how I would do it?

art297
Opal | Level 21

Did you convert your data points to log as in your original example?  If not, try it with:

data need;

  set have;

  array vars(*) a1--a100;

  yrmon=put(DATE,yymmd.);

  do i=1 to dim(vars);

    vars(i)=log(vars(i));

  end;

run;

proc means data=need mean uss nway;

  var a1--a100;

  class yrmon;

  output out=want (drop=_:)

   mean= uss= /autoname;

run;

art297
Opal | Level 21

Try it with the following syntax:

proc means data=need mean uss nway;

  var a1--a100;

  class yrmon;

  output out=want (drop=_:)

   mean= uss= /autoname;

run;

Costasg
Calcite | Level 5

I tried both ways but I still get diferrent reasults. Maybe it is the methodology of the paper that I am using different.

What I need to do is: let Pt the price of stock today.

1. convert prices: [log(Pt)-log(Pt-1)]*100 (let's call it X)

2.get the average of each month (lets call it x)

3. substract each X by the average. Xt-x (let's call it y)

4.miltiply y*y.

5.Finally get the average of the above for each month to get the monthly volatility.

art297
Opal | Level 21

Don't think I can be of further help then.  In your 2:30pm post, yesterday, you indicated that you were starting with a table that contained the log differences.  Thus, there shouldn't be any need to take the log of the values or use the lag function in order to obtain the difference amounts.

The calculation of the average (i.e., mean) and sum of squared differences from the mean (i.e., uss) are standard calculations and wouldn't differ between SAS and Excel.  You could be using different log functions, or the paper might be taking the volatility calculation one step further and dividing the uss by n-1 and obtaining the variance.

Costasg
Calcite | Level 5

That's ok art297, many thanks for your help! Maybe SAS calculates the avarege of the whole time series and substracts it from the observations instead of calculating each month's seperetaly. I will try to find out why I get different results.

Many thanks again

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 7814 views
  • 6 likes
  • 2 in conversation