Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- create monthly decile Portfolios sorted on risk (3-years sorted by vol...

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

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

Posted 08-25-2019 06:38 AM
(2967 views)

Hello dear People,

I want to create 10 decile Portfolios from my datasets Europe Stoxx 300. i calculated the equal weighted Returns and the LogReturns as you can see on my Excel-data , but now i don't know how to sort this by Volatility. Please help me. Thanks you!

1 ACCEPTED SOLUTION

Accepted Solutions

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

I believe you've missed that the last Proc Import I've posted didn't read in the data 100% how you need it. Especially the Firm names didn't make it into SAS. Below an amended version which should do the job.

I won't be doing all the work for you. The code below covers the first and likely hardest bit for you. It returns the deciles per month and is close to your *first table* sample. Please note that I'm transposing the data into a long format as this is in most cases a much better data structure to work with.

```
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 didn't really understand why in your *first table* the volatility in D1 could be higher than in D10.

Below what Proc Print returns. You can of course create more elaborate reports using Procedures like Proc Report or Proc Tabulate.

Proc Rank returns the deciles with a number between 0-9. If you want these numbers to print as D1-D10 then create a format and apply this format to variable Decile for printing.

17 REPLIES 17

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

I've typed into Google: SAS decile

First hit I've got: https://communities.sas.com/t5/SAS-Procedures/Splitting-a-data-set-into-10-equal-deciles/td-p/267187

Hope that helps.

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

thanks for your Help Patrick, i'm trying.

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

Hey Dear members,

i tried to calculate my volatility from my data entered in SAS but i front many Problems.

first the date change on numerical values for example

for 31.12.1997 ( Excel) to 35795 ( SAS) |

i don`t understand why?

second the values are converted again in 100% for exampe for -0.00928714889489078% ( Excel) to -9.287148894890781E-5 (SAS) , i don't need that why my data already are in percent.

for the calcul of my volatility i used first proc means but my Programm Shows me Errors, it doesn't recognize the command Rtnvol for volatility.

please can someone help me to unterstand what happens on my Programm and Shows me how i can calculate the volatility and sort my data on decile Portfolios sorted on 3- years volatility. i will be very grateful for your Help. Ths.

Errors showed

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 proc means

74

75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

_______

22

76

ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ;, ALPHA, CHARTYPE, CLASSDATA, CLM, COMPLETETYPES, CSS, CV, DATA,

DESCEND, DESCENDING, DESCENDTYPES, EXCLNPWGT, EXCLNPWGTS, EXCLUSIVE, FW, IDMIN, INCAS, KURTOSIS, LCLM, MAX, MAXDEC,

MEAN, MEDIAN, MIN, MISSING, MODE, N, NDEC, NMISS, NOLABELS, NONOBS, NOPRINT, NOTHREADS, NOTRAP, NWAY, ORDER, P1, P10,

P20, P25, P30, P40, P5, P50, P60, P70, P75, P80, P90, P95, P99, PCTLDEF, PRINT, PRINTALL, PRINTALLTYPES, PRINTIDS,

PRINTIDVARS, PROBT, Q1, Q3, QMARKERS, QMETHOD, QNTLDEF, QRANGE, RANGE, SKEWNESS, STACKODS, STACKODSOUTPUT, STDDEV,

STDERR, SUM, SUMSIZE, SUMWGT, T, THREADS, UCLM, USS, VAR, VARDEF.

ERROR 76-322: Syntax error, statement will be ignored.

95 proc means data=rtnvol.rtnvol std;

ERROR: Libref RTNVOL ist nicht zugewiesen.

96

97 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

109

many thanks for your Help.

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

Please share your program, SAS Log and sample data (source Excel) to allow us to replicate the issue and to test amended and working code.

first the date change on numerical values for example for 31.12.1997 ( Excel) to 35795 ( SAS)

SAS stores dates as the count of days since 1/1/1960. You then apply a SAS format to print such a number human readable as a date string. ...but: The number you show us would return a different date than what Excel shows you.

Not sure if anyone will be able to guess what's going wrong for you without seeing your code and some of the data.

ERROR 22-322: Syntaxfehler,....

The SAS log tells you that there is a syntax error in your code but you haven't shared neither the code nor all of the relevant SAS log section with us.

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

```
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 /* Generierter Code (IMPORT) */
74 /* Quelldatei: SaS_Stoxx300.xlsx */
75 /* Quellpfad: /folders/myshortcuts/MyFolders */
76 /* Code generiert am: 04.09.19 23:51 */
77
78 %web_drop_table(WORK.IMPORT);
NOTE: Table WORK.IMPORT has been dropped.
NOTE: Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
real time 0.01 seconds
cpu time 0.02 seconds
79
80
81 FILENAME REFFILE '/folders/myshortcuts/MyFolders/SaS_Stoxx300.xlsx';
82
83 PROC IMPORT DATAFILE=REFFILE
84 DBMS=XLSX
85 OUT=WORK.IMPORT;
86 GETNAMES=NO;
87 SHEET="LogReturn";
88 RUN;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 262 observations and 304 variables.
NOTE: WORK.IMPORT data set was successfully created.
NOTE: Verwendet wurde: PROZEDUR IMPORT - (Gesamtverarbeitungszeit):
real time 3.20 seconds
cpu time 3.09 seconds
89
90 PROC CONTENTS DATA=WORK.IMPORT; RUN;
NOTE: Verwendet wurde: PROZEDUR CONTENTS - (Gesamtverarbeitungszeit):
real time 2.46 seconds
cpu time 2.45 seconds
91
92
93 %web_open_table(WORK.IMPORT);
94
95 proc means
96
97 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
_______
22
76
ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ;, ALPHA, CHARTYPE, CLASSDATA, CLM, COMPLETETYPES, CSS, CV, DATA,
DESCEND, DESCENDING, DESCENDTYPES, EXCLNPWGT, EXCLNPWGTS, EXCLUSIVE, FW, IDMIN, INCAS, KURTOSIS, LCLM, MAX, MAXDEC,
MEAN, MEDIAN, MIN, MISSING, MODE, N, NDEC, NMISS, NOLABELS, NONOBS, NOPRINT, NOTHREADS, NOTRAP, NWAY, ORDER, P1, P10,
P20, P25, P30, P40, P5, P50, P60, P70, P75, P80, P90, P95, P99, PCTLDEF, PRINT, PRINTALL, PRINTALLTYPES, PRINTIDS,
PRINTIDVARS, PROBT, Q1, Q3, QMARKERS, QMETHOD, QNTLDEF, QRANGE, RANGE, SKEWNESS, STACKODS, STACKODSOUTPUT, STDDEV,
STDERR, SUM, SUMSIZE, SUMWGT, T, THREADS, UCLM, USS, VAR, VARDEF.
ERROR 76-322: Syntax error, statement will be ignored.
98 ODS HTML CLOSE;
99 &GRAPHTERM; ;*';*";*/;RUN;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Verwendet wurde: PROZEDUR MEANS - (Gesamtverarbeitungszeit):
real time 0.11 seconds
cpu time 0.02 seconds
99 ! QUIT;
100 QUIT;RUN;
101 ODS HTML5 (ID=WEB) CLOSE;
102
103 ODS RTF (ID=WEB) CLOSE;
104 ODS PDF (ID=WEB) CLOSE;
NOTE: ODS PDF(WEB) printed 9 pages to /tmp/SAS_work4A5400000B32_localhost.localdomain/#LN00060.
105 FILENAME _GSFNAME;
NOTE: Fileref _GSFNAME has been deassigned.
106 DATA _NULL_;
107 RUN;
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 0.00 seconds
cpu time 0.01 seconds
108 OPTIONS NOTES STIMER SOURCE SYNTAXCHECK;
109
```

this is my Log above and my Excel Datei you will see in the Attachments, thanks for trying help me Patrick.

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

Looking into your Excel it appears that the data only starts on row 4.

The DATAROW=4 statement will instruct SAS to only analyze and read data from row 4. Without this statement Proc Import will read data from row 1 if getnames=no and from row 2 if getnames=yes. And if reading from row 1 or 2 already SAS will find for all columns at least one occurrence where there are strings in a cell and though read all the data as strings and create for all columns SAS character variables.

Below code should read your Excel source as you need it.

```
FILENAME REFFILE '/folders/myshortcuts/MyFolders/SaS_Stoxx300.xlsx';
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT
replace;
GETNAMES=yes;
SHEET="LogReturn";
datarow=4;
RUN;
```

You get the syntax error because you've got a PROC MEANS in your code which is not ended with a semicolon - and then an Options statement. For SAS this looks like you want to use a keyword of OPTIONS as something that belongs to the Proc Means procedure. ....and as no such keyword exists for Proc Means SAS tells you that it's a syntax error and then provides a list of valid keywords.

94 95 proc means 96 97 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; _______ 22 76 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ;, ALPHA, CHARTYPE, CLASSDATA, CLM, COMPLETETYPES, CSS, CV, DATA, DESCEND, DESCENDING, DESCENDTYPES, EXCLNPWGT, EXCLNPWGTS, EXCLUSI

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

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

There is nothing to apologize for.

You need to write a proper Proc Means block similar to your Proc Import. If you're new to SAS and using a client like SAS EG or SAS Studio then eventually use one of the tasks/wizard first to click "together" a report. This will generate you valid SAS syntax which you then still can change manually if required.

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

thanks Patrick, it's going well now with the semicolomn and i get this result below as you can see on my Attachments. the second step now will be which'Code will be needed to calcul my volatility and sort the data on the past 3 years volatility. i tried this after proc means in the follow Code but get this result with error.

```
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 /* Generierter Code (IMPORT) */
74 /* Quelldatei: SaS_Stoxx300.xlsx */
75 /* Quellpfad: /folders/myshortcuts/MyFolders */
76 /* Code generiert am: 04.09.19 23:51 */
77
78 %web_drop_table(WORK.IMPORT);
NOTE: Table WORK.IMPORT has been dropped.
NOTE: Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
real time 0.00 seconds
cpu time 0.01 seconds
79
80
81 FILENAME REFFILE '/folders/myshortcuts/MyFolders/SaS_Stoxx300.xlsx';
82
83 PROC IMPORT DATAFILE=REFFILE
84 DBMS=XLSX
85 OUT=WORK.IMPORT
86 Replace;
87 GETNAMES=NO;
88 SHEET="LogReturn";
89 datarow=4;
90
91 RUN;
NOTE: The import data set has 259 observations and 304 variables.
NOTE: WORK.IMPORT data set was successfully created.
NOTE: Verwendet wurde: PROZEDUR IMPORT - (Gesamtverarbeitungszeit):
real time 3.31 seconds
cpu time 3.02 seconds
92
93 PROC CONTENTS DATA=WORK.IMPORT; RUN;
NOTE: Verwendet wurde: PROZEDUR CONTENTS - (Gesamtverarbeitungszeit):
real time 2.08 seconds
cpu time 2.03 seconds
94
95
96 %web_open_table(WORK.IMPORT);
97
98
99 proc means;
100
NOTE: There were 259 observations read from the data set WORK.IMPORT.
NOTE: Verwendet wurde: PROZEDUR MEANS - (Gesamtverarbeitungszeit):
real time 5.72 seconds
cpu time 5.72 seconds
101 Proc summary data=rtnvol.rtnvol nway;
ERROR: Libref RTNVOL ist nicht zugewiesen. ( that mean : Libref RTNVOL is not assigned)
102
103
104 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
116
```

thanks in Advance!

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

The easiest way to answer your question:

We've got your source data. Now show us exactly how the desired result should look like using this sample data. Please use the exactly same variable names. Just post a table with sufficient content that let's us understand how source maps to target.

Provide also some narrative explaining the logic how you get from your source to your target.

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

Hi Patrick,

Thanks for replying my Questions. i added on the Attachments the Excel-data and explained how i would like to have the result.

Thanks for hear you soon.

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

I believe you've missed that the last Proc Import I've posted didn't read in the data 100% how you need it. Especially the Firm names didn't make it into SAS. Below an amended version which should do the job.

I won't be doing all the work for you. The code below covers the first and likely hardest bit for you. It returns the deciles per month and is close to your *first table* sample. Please note that I'm transposing the data into a long format as this is in most cases a much better data structure to work with.

```
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 didn't really understand why in your *first table* the volatility in D1 could be higher than in D10.

Below what Proc Print returns. You can of course create more elaborate reports using Procedures like Proc Report or Proc Tabulate.

Proc Rank returns the deciles with a number between 0-9. If you want these numbers to print as D1-D10 then create a format and apply this format to variable Decile for printing.

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

thanks you so much Patrick, you're someone Special!:)

About your Question: " I didn't really understand why in your *first table* the volatility in D1 could be higher than in D10." It was just some example taking by me, so for the volatility effect like the authors say. the volatility of this Decil portfolio D1 should be smaller than of the volatility of the higher Decil Portfolio D10, so that is what i Need to prouve on my Thesis. i'm so grateful of your Help. so I'm trying now all the Code you sended me and come back to you by any other Questions because i Need for this founded result a descriptive Analysis. Thanks you and good day or night!

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

If you have a follow-up question then please start this as a new question - eventually posting a reference link to what's here.

If it's more of an analytics question then post it into the analytics forum - and same for reporting into a reporting forum.

This gives you a better chance that the right people look into it.

**Available on demand!**

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

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.