全部版块 我的主页
论坛 数据科学与人工智能 数据分析与数据科学 SAS专版
2041 1
2010-05-25
悬赏 10 个论坛币 未解决
Hi,

I have a daily stock price database that is not complete (i.e. with gaps
between dates). I need a code that can, for each ticker and date in the
dataset, calculate the return between that date and N days prior to that.
(N=1, 3, 7, ...etc). I can't use the LAG function since the dates are not
continuous and I'm wondering if there's a way to somehow combine LAG and
INTCK. The dataset is as follows (suppose there are no weekends):

Ticker  Date        Price
ABC   1/1/2002  10
ABC   1/2/2002  11
ABC   1/5/2002  12  (NOTE THE 2-day GAP HERE!!!!!)
ABC   1/6/2002  13
ABC   1/7/2002  14
DEF    1/1/2002
.........................

and now let's say I want to calculate the 3-day return. I'd like SAS to
automatically calculate the number of days between the current date and pick
the correct reference date to calculate the return. LAG function cannot do
that. Specifically, I'd like the output to be

Ticker  Date       Return
ABC   1/1/2002  .
ABC   1/2/2002  .
ABC   1/5/2002  12/11-1 (CORRECT 3-day return)
ABC   1/6/2002  .
ABC   1/7/2002  .
DEF    1/1/2002 .
.........................

INSTEAD OF the one calculated with a LAG function:

Ticker  Date       Return
ABC   1/1/2002  .
ABC   1/2/2002  .
ABC   1/5/2002  12/10-1 (INCORRECT return)
ABC   1/6/2002  13/11-1
ABC   1/7/2002  14/12-1
DEF    1/1/2002 .
.........................

I know INTCK and INTNX is supposed to do the trick but I've been having a
hard time figuring out how to incorporate that into the lag calculations.

Any help is greatly appreciated!
二维码

扫码加我 拉你入群

请注明:姓名-公司-职位

以便审核进群资格,未注明则拒绝

全部回复
2010-5-26 10:56:09
I hesitate to use LAG... I would try something like this:

data aaa;
input ticker $ date mmddyy10. price;
format date mmddyy10.;
cards;
ABC   1/1/2002  10
ABC   1/2/2002  11
ABC   1/5/2002  12
ABC   1/6/2002  13
ABC   1/7/2002  14
DEF   1/1/2002  9
DEF   1/2/2002  11
DEF   1/5/2002  13
DEF   1/6/2002  15
DEF   1/7/2002  18
;
run;
proc sort; by ticker date; run;

* assign n=1,3,5 etc,  n=1 means return between that date and 1 business day prior * ;
%let nday = 1;

data aaa1;
set aaa;
by ticker date;
if first.ticker then n = 1;
else n+1;
seq = mod(n,&nday);
run;
proc sort; by ticker seq date; run;

data bbb;
set aaa1;
by ticker seq date;
retain temp;
if first.seq then do; temp = price; return = .; end;
else do;
        return = price/temp - 1;
        temp = price;
end;
drop temp seq n;
run;
proc sort; by ticker date; run;
               
有点啰嗦,见谅哈!
二维码

扫码加我 拉你入群

请注明:姓名-公司-职位

以便审核进群资格,未注明则拒绝

相关推荐
栏目导航
热门文章
推荐文章

说点什么

分享

扫码加好友,拉您进群
各岗位、行业、专业交流群