我正在尝试在 SAS 中解决以下问题。
我有几只股票的每日回报数据。这是我的数据的样子(简化):
PERMNO DATE RETURN
10078 2010JAN02 0.0500
10104 2010JAN02 -0.0190
10107 2010JAN02 0.0020
10078 2010JAN03 0.0040
10104 2010JAN03 -0.0400
10107 2010JAN03 0.0500
... ... ...
10078 2015JAN02 -0.0190
10104 2015JAN02 0.0100
10107 2015JAN02 0.0700
10078 2015JAN05 0.0500
10104 2010JAN03 -0.0190
10107 2010JAN03 0.0020
PERMNO标识股票,DATE标识日期(yyyymmmdd),RETURN是每日股票回报。 我只为三只股票(10078、10104、10107)简化了这个例子。
目标:我正在尝试计算给定月份 t 中每只股票 i 的滚动偏度。 我想使用过去 6 个月(即 t-6 到 t-1 月份)的每日回报数据来计算每只股票的每月偏度指标。因此,对于例如 2010 年 7 月的股票,我希望该月的偏度指标基于其 2010 年 1 月至 2010 年 6 月的每日回报。
我希望输出数据包括该月的 PERMNO、月份 ID 和每月偏度度量(基于前 6 个月的数据)。这是一张图片来说明我想要的所需输出:
PERMNO DATE 6MONTH_SKEWNESS
10078 2010JUL30 0.7257
10104 2010JUL30 -0.7056
10107 2010JUL30 -0.6781
10078 2010AUG31 0.9999
10104 2010AUG31 -0.6719
10107 2010AUG31 -0.7056
... ... ...
10078 2015JUL30 -0.1651
10104 2015JUL30 0.1056
10107 2015JUL30 0.6181
10078 2015AUG31 -0.8886
10104 2015AUG31 0.6119
10107 2015AUG31 0.1056
我已经在网上广泛搜索并自己尝试过这个,但我真的觉得这个问题很困难。提前感谢任何能够以任何方式提供帮助的人。
更新:这是我正在使用的修改后的代码。非常感谢用户667489。
/*Join on the previous 6 months of data for each stock*/
proc sql;
create view rolling as
select
a.PERMNO, /* PERMNO is the variable that identifies a stock */
a.DATE as MONYR_A label="", /* DATE is a date variable for each daily return */
b.DATE as MONYR_B label="",
b.RET /* RET is the variable containing daily returns */
from Dsex3 a left join Dsex3 b /* Dsex3 is the dataset that contains the identifiers for stocks and their daily returns */
on a.PERMNO = b.PERMNO
and 1 <= intck('month',b.date,a.date) <= 6
group by a.PERMNO, MONYR_A
having count(*) >= 6 /*Only calculate skewness if we have at least 6 months of data*/
order by a.PERMNO desc,MONYR_A,MONYR_B;
quit;
/*Calculate the skewness for the previous 6 months worth of data*/
ods listing close;
ods output moments=skewness_summary(
keep = PERMNO MONYR_A label1 nvalue1
where=(label1='Skewness')
rename = (nvalue1 = skewness_6mth)
);
proc univariate data = rolling;
var RET;
by descending PERMNO MONYR_A;
run;
ods listing;
如果您没有 SAS/ETS,您可以尝试如下操作:
/*Generate some dummy data from the sashelp area*/
proc summary data = sashelp.prdsal2 nway;
var ACTUAL;
class COUNTRY STATE PRODUCT MONYR;
output out = prdsal2(drop = _:) sum=;
run;
/*Join on the previous 6 months of data for each product*/
proc sql;
create view rolling as
select
a.COUNTRY,
a.STATE,
a.PRODUCT,
a.MONYR as MONYR_A label="",
b.MONYR as MONYR_B label="",
b.ACTUAL
from prdsal2 a left join prdsal2 b
on a.COUNTRY = b.COUNTRY
and a.STATE = b.STATE
and a.PRODUCT = b.PRODUCT
and 1 <= intck('month',b.MONYR,a.MONYR) <= 6
group by a.COUNTRY,a.STATE,a.PRODUCT,MONYR_A
having count(*) >= 6 /*Only calculate skewness if we have at least 6 months of data*/
order by a.COUNTRY desc,a.STATE,a.PRODUCT,MONYR_A,MONYR_B;
quit;
/*Calculate the skewness for the previous 6 months worth of data*/
ods listing close;
ods output moments=skewness_summary(
keep = COUNTRY STATE PRODUCT MONYR_A label1 nvalue1
where=(label1='Skewness')
rename = (nvalue1 = skewness_6mth)
);
proc univariate data = rolling;
var ACTUAL;
by descending COUNTRY STATE PRODUCT MONYR_A;
run;
ods listing;
我发现 SAS 有一个实际的skewness
函数,可以直接为变量列表计算它,所以这里也是一种数据步骤方法:
/*Data step approach, using the skewness function*/
data skewness_summary2;
do _n_ = 1 by 1 until(last.MONYR_A);
set rolling(keep = COUNTRY STATE PRODUCT MONYR_A ACTUAL);
by descending COUNTRY STATE PRODUCT MONYR_A;
skewness_6mth = skewness(ACTUAL,lag1(ACTUAL),lag2(ACTUAL),lag3(ACTUAL),lag4(ACTUAL),lag5(ACTUAL));
if _n_ >= 6 then output;
end;
run;
数据步长方法似乎比使用 proc 单变量快一点,可能是因为 proc 也在计算所有其他时刻。