請問如何定義出各股票從1963年至2023年歷史過去的最大價格,並創建新變數BREAKHIGH來表示價格當該日收盤價突破歷史高點後,計算突破歷史高點後該股票的六個月報酬?感謝!
the steps I've already tried as belows:
Try this code:
DATA STOCK;
SET HIGH.Short;
RUN;
proc sort data=STOCK;
by PERMNO date;
run;
DATA STOCK1;
SET STOCK;
PRC=ABS(PRC);
KEEP PERMNO DATE PRC;
RUN;
data stock2;
set stock1;
by PERMNO;
retain max_PRC;
if first.PERMNO then call missing(max_PRC);
max_PRC=max(max_PRC,PRC);
run;
data stock3;
set stock2;
if PRC=max_PRC then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct PRC from stock3 having PERMNO=a.PERMNO and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-PRC)/PRC
) else . end as return
from stock3 as a;
quit;
About your question of highest price, it is the last "BREAKHIGH=1" within each PERMNO in my code.
/*
1)Why not post some sample data by sas code NOT picture ? so we can test code for it.
2)Better post your question in English, Most of sas user here are not from China, are unable to read Chinese.
*/
data stock;
set sashelp.stocks;
keep stock close date;
run;
proc sort data=stock;
by stock date;
run;
data stock2;
set stock;
by stock;
retain max_close .;
if first.stock then call missing(max_close);
max_close=max(max_close,close);
run;
data stock3;
set stock2;
if close=max_close then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct close from stock having stock=a.stock and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-close)/close
) else . end as return
from stock3 as a;
quit;
Thank you
May I know how to further solve the following?
1.NOTE: Variable 'first.stock'n is uninitialized.
2.NOTE: The query requires remerging summary statistics back with the original data.
ERROR: Subquery evaluated to more than one row.
DATA STOCK;
SET HIGH.Short;
RUN;
DATA STOCK1;
SET STOCK;
PRC=ABS(PRC);
KEEP PERMNO DATE PRC;
RUN;
PROC SORT DATA= STOCK1;
BY PERMNO DATE;
RUN;
data stock2;
set stock1;
by PERMNO;
retain max_PRC;
if first.stock then call missing(max_close);
max_PRC=max(max_PRC,PRC);
run;
data stock3;
set stock2;
if PRC=max_PRC then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct PRC from stock having PERMNO=a.PERMNO and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-PRC)/PRC
) else . end as return
from stock3 as a;
quit;
Use of FIRST. and LAST. variables requires the variable to be on the BY statement.
data stock2; set stock1; by PERMNO Stock; retain max_PRC; if first.stock then call missing(max_close); max_PRC=max(max_PRC,PRC); run;
If the data is not sorted by the combination of variables but is grouped you may need the NOTSORTED option on the BY statement.
Thank you for your reply!
I delete the following, but still
NOTE: The query requires remerging summary statistics back with the original data.
ERROR: Subquery evaluated to more than one row.
PROC SORT DATA= STOCK1;
BY PERMNO DATE;
RUN;
DATA STOCK;
SET HIGH.Short;
RUN;
DATA STOCK1;
SET STOCK;
KEEP PERMNO DATE PRC;
RUN;
data stock2;
set stock1;
by PERMNO;
retain max_PRC;
if first.PERMNO then call missing(max_close);
max_PRC=max(max_PRC,PRC);
run;
data stock3;
set stock2;
if PRC=max_PRC then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct PRC from stock having PERMNO=a.PERMNO and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-PRC)/PRC
) else . end as return
from stock3 as a;
quit;
https://drive.google.com/file/d/1ayhUMgH0j2IuIykNpgUt6WbPg-7Gx-JA/view
Thank you for your reply!
but make PERMNO=stock won't confuse SAS? Cause file name is also named as stock.
DATA STOCK1(rename=(PERMNO=stock PRC=close ) );
SET STOCK;
DATA STOCK;
SET HIGH.Short;
RUN;
DATA STOCK1;
SET STOCK;
PRC=ABS(PRC);
KEEP PERMNO DATE PRC;
RUN;
data stock2;
set stock1;
by PERMNO;
retain max_PRC;
if first.PERMNO then call missing(max_close);
max_PRC=max(max_PRC,PRC);
run;
data stock3;
set stock2;
if PRC=max_PRC then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct PRC from PERMNO having PERMNO=a.PERMNO and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-PRC)/PRC
) else . end as return
from stock3 as a;
quit;
Thank you!
Actually,I want SAS program that define the highest historical prices of each stock from 1963 to 2023, and create a new variable BREAKHIGH to represent when the closing price breaks through the historical high. After breaking through the historical high, calculate the six-month return of that stock. I want to divide into 2 group by breakthrough historical high and the rest,and run the excess return by 3/4/5 factor model by this two group.
the first obs be a breakthrough high should be after one year historical data.
Try this code:
DATA STOCK;
SET HIGH.Short;
RUN;
proc sort data=STOCK;
by PERMNO date;
run;
DATA STOCK1;
SET STOCK;
PRC=ABS(PRC);
KEEP PERMNO DATE PRC;
RUN;
data stock2;
set stock1;
by PERMNO;
retain max_PRC;
if first.PERMNO then call missing(max_PRC);
max_PRC=max(max_PRC,PRC);
run;
data stock3;
set stock2;
if PRC=max_PRC then BREAKHIGH=1;
run;
proc sql;
create table want as
select *,case when BREAKHIGH=1 then (
((select distinct PRC from stock3 having PERMNO=a.PERMNO and abs(date-intnx('month',a.date,6,'s'))=min(abs(date-intnx('month',a.date,6,'s'))))-PRC)/PRC
) else . end as return
from stock3 as a;
quit;
About your question of highest price, it is the last "BREAKHIGH=1" within each PERMNO in my code.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.