請問如何計算歷史過去的最大價格，並創建新變數BREAKHIGH來表示價格是否突破歷史高點

[Irenelee]

請問如何定義出各股票從1963年至2023年歷史過去的最大價格，並創建新變數BREAKHIGH來表示價格當該日收盤價突破歷史高點後，計算突破歷史高點後該股票的六個月報酬?感謝!

the steps I've already tried as belows:

DATA CRSP;

SET HIGH.DAILY;

RUN;

DATA CRSP;

SET  CRSP;

YMD=10000*YEAR(DATE)+100*MONTH(DATE)+DAY(DATE);

PRC=ABS(PRC);

IF SHRCD^=10 AND SHRCD^=11 THEN DELETE;

KEEP CUSIP PERMNO YMD PRC YEAR YEAR2  RET;

RUN;

PROC SORT DATA= CRSP;

BY PERMNO YMD;

RUN;

proc sql;

select PERMNO, max(PRC) 

from CRSP;

/*group by PERMNO;*/

quit;

data CRSP;

set CRSP;

if PRC > max(PRC) and YMD  then BREAKHIGH = 1;

else BREAKHIGH = 0;

run;

[Ksharp]

/*
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;

[Irenelee]

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;

[ballardw]

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.

[Irenelee]

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 

[Ksharp]

Since here your PERMNO is stock name (is same with my STOCK), PRC is stock price(is same with my CLOSE),
You could rename them as mine (only keep three variable STOCK DATE CLOSE)and run the following code of mine.

DATA STOCK1(rename=(PERMNO=stock PRC=close ) );
SET STOCK;
PRC=ABS(PRC);
KEEP PERMNO DATE PRC;
RUN;

[Irenelee]

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;

[Irenelee]

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.

[Ksharp · Accepted solution]

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.

[Ksharp]