splitting data sets into 10 decile portfolios ranked on the past 3 year volatility

[MarthelF]

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.   

[mkeintz]

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

[MarthelF]

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]

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.

[MarthelF]

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

[mkeintz]

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.

[MarthelF]

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!