Accepted Solutions
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.
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
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. 
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.
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
6042 views
-
1 like
-
-
