BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MarthelF
Calcite | Level 5

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
Patrick
Opal | Level 21

@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.

 

Capture.JPG

View solution in original post

17 REPLIES 17
MarthelF
Calcite | Level 5

thanks for your Help Patrick, i'm trying.  

MarthelF
Calcite | Level 5

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.

Patrick
Opal | Level 21

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. 

MarthelF
Calcite | Level 5
 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.

Patrick
Opal | Level 21

@MarthelF 

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

Capture.JPG

 

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

 

 

MarthelF
Calcite | Level 5

thanks you patrickSmiley Very Happy 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.  

Patrick
Opal | Level 21

@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.

 

MarthelF
Calcite | Level 5

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!

Patrick
Opal | Level 21

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. 

MarthelF
Calcite | Level 5

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.Smiley Happy

Patrick
Opal | Level 21

@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.

 

Capture.JPG

MarthelF
Calcite | Level 5

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! 

Patrick
Opal | Level 21

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.

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!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 2968 views
  • 1 like
  • 2 in conversation