Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Analytics
- /
- Stat Procs
- /
- splitting data sets into 10 decile portfolios ranked on the past 3 yea...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-06-2019 06:43 PM
(837 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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!

**Available on demand!**

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

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.