topic Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility) in SAS Programming

create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Sun, 25 Aug 2019 10:38:34 GMT

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!

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Sun, 25 Aug 2019 11:30:53 GMT

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Sun, 25 Aug 2019 12:32:01 GMT

thanks for your Help Patrick, i'm trying.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Wed, 04 Sep 2019 12:29:43 GMT

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;

73 proc means

75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

_______

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.

97 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

109

many thanks for your Help.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Wed, 04 Sep 2019 21:15:53 GMT

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Wed, 04 Sep 2019 22:20:46 GMT

 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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Wed, 04 Sep 2019 23:02:27 GMT

@MarthelF

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

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Wed, 04 Sep 2019 23:03:30 GMT

thanks you patrick i didn´t know that, thanks so much. i will bring on my Code this new command, sorry About the command Proc means to make another calcul of the volatility, i'm confused, you mean i will end it with semicolumn right? thanks you in Advance.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Wed, 04 Sep 2019 23:15:34 GMT

@MarthelF

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Thu, 05 Sep 2019 00:33:07 GMT

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!

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Thu, 05 Sep 2019 00:44:58 GMT

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Thu, 05 Sep 2019 12:41:57 GMT

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Fri, 06 Sep 2019 00:10:31 GMT

@MarthelF

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Fri, 06 Sep 2019 01:32:13 GMT

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!

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Fri, 06 Sep 2019 01:44:00 GMT

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.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Fri, 06 Sep 2019 03:18:53 GMT

ok Patrick, thanks. Sorry but i verified and applied the Code, i think Maybe you have forgotten to calculate the Volatility. If i have very well unterstood the Code you have assigned at a new colomn the Name Volatility but when i checked the numeric data, they are remained unchanged. can't you please checked this? thanks you in Advance.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

Patrick — Fri, 06 Sep 2019 04:19:13 GMT

@MarthelF

I thought you've calculated the volatility already and now need just the ranking.

What you're doing here is not really in my area of expertise so I suggest you split up your problem and then ask new questions one step at a time.

I'd start with the transformed data structure (giving the Volatility column the appropriate name) and describe the logic you require to get from the "have" data to a column containing the volatility.

Re: create monthly decile Portfolios sorted on risk (3-years sorted by volatility)

MarthelF — Fri, 06 Sep 2019 10:52:16 GMT

thanks for your Proposition Patrick. and many thanks for your help!