BookmarkSubscribeRSS Feed
MarthelF
Calcite | Level 5

hey People,

 

i have a data sets of 303 firms from europe and want to create 10 decile Portfolio ranked on the past 3 year volatility. each Portfolio should be created at the end of each month. i have already calculated the equal weighted return on my Excel data on the Attachments, and i tried to sort the data on decile as you can see on my SAS Code but the Problem is that i just created the colomn named volatility but i Need now to calculate the volatility because all the data inside my table are uncorrect and are the same data of LogReturn . thanks for your Help!

FILENAME REFFILE '/folders/myshortcuts/MyFolders/SaS_Stoxx300.xlsx';
PROC IMPORT DATAFILE=REFFILE
  DBMS=XLSX
  OUT=WORK.IMPORT
  replace;
  GETNAMES=yes;
  SHEET="LogReturn";
  RANGE='a3:zz99999999999'n;
RUN;

proc sort data=work.import(keep=_numeric_);
  by date;
run;

proc transpose 
      data=work.import  
      out=work.transposed(rename=(_label_=Firm Volatility1=Volatility) where=(not missing(Volatility)) drop=_name_)
      prefix=Volatility;
  by date;
run;

proc rank data=work.transposed groups=10 descending out=work.ranked;
  var Volatility;
  ranks Decile;
  by date;
run;

proc sort data=work.ranked;
  by date decile;
run;

proc print data=work.ranked;
  by date;
run;

. I will be grateful on your Help. thanks you in Advance.   

6 REPLIES 6
mkeintz
PROC Star

Could you show a sample of what your work.import looks like?  This would make it easier to understand how to implement your objective.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
MarthelF
Calcite | Level 5

Hi Keintz, i'm trying to upload what you mean as a Photos, i get error. but i think i you apply with my data source from Excel and the SAS Code on your Pc you'll get this Work.import. Thanks!

mkeintz
PROC Star

I have not been on this forum for several months, so perhaps I'm not seeing what is there.  But I do NOT see a link to an uploaded spreadsheet that you tells me to use.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
MarthelF
Calcite | Level 5

sorry Maybe i've forgotten to upload the Excel Data, i've done it below. thanks!

mkeintz
PROC Star

OK, so you have a variable DATE, plus 303 other variables with monthly returns.  And you want volatility for each security over rolling 36-month windows, is that correct?  If so, then you want to establish rolling standard deviations prior to transpose.

 

Do you have a SAS/ETS license?  ETS (econometric-time-series) includes PROC EXPAND, which is intended for such tasks as generating statistics for rolling windows.

 

And in the end, you may not need proc transpose.  Your program attempts to make 303 observations for each date, containing the stock id and a decile. Or would you rather have one observations for each date, containing 303 decile assignment?  If it's the latter, you don't need the transpose.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
MarthelF
Calcite | Level 5

Hi mKeintz,

About your first Question correct! so i want to establish rolling window sd for each stock, then create volatility deciles in each month.  for example this is the Code for stata (bys month_variable: astile vol_decile=sd_3, nq(10), then create equal-weighted Returns for each decile. bys vol_deciles month_variable: egen Portfolio_ret = mean(Returns), then reduce the data to the Portfolio Level. bys vol_decil vol_deciles month_variable:keep if _n= _N. About your Question: Do you have a SAS/ETS license?  ETS (econometric-time-series) includes PROC EXPAND, which is intended for such tasks as generating statistics for rolling Windows!, i have SAS University Edition, so i don't know if it Permits me to do that, if not could you tell me please what to do to get SAS/ETS License?. About your Question: would you rather have one observations for each date, containing 303 decile assignment? what i Need at the end is to have decile Portfolio like this example: https://www.screencast.com/t/kxnHXgEs 

can you please help me mKeintz, i Need this Work for my Thesis and i have just 3 weeks left. Thanks in Advance!

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 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
  • 6 replies
  • 838 views
  • 0 likes
  • 2 in conversation