Months since 52-week high

Accepted Solution Solved
Reply
Contributor ZZB
Contributor
Posts: 34
Accepted Solution

Months since 52-week high

I want to calculate months since recent 12-month (52-week) high. See the sample data below. I spend much time on this problem but cannot figure it out. 

 

(1). For stock '10001', I get the recent 52-week high (high12month) for 2011/12 whose data for calculation is from 2011/1 to 2011/12. The 11.86989975 is the 52-week high for 2011/12. The highest price appears in 2011/7. The 'High' is highest price in current month. The time for 52-week high is 12 months.

 

(2).  For 2012/1, the sample data for 52-week high is from 2011/2-2012/1. During this period, the highest price still appears in 2011/7, so the 'high12month' is still 11.86989975 for 2012/1.

vs.   For 2012/7, the calculation data is from 2011/8-2012/7 in which the highest price (11.65999847) appears in 2012/3, so the 'High12month' for 2012/7 is 11.65999847.

 

(3). For 2011/12, I want to get the Months/Gap since the Highest price during 2011/1-2011/12 first appears (in this case, 2011/7). In this case, the Months since 52-week high is 2011/12-2011/7=5.

For 2012/1, the Months since recent 12-month high is 2012/1-2011/7=6 because 2011/7 is still between 2011/2-2012/1. 

For 2012/7, the Months is 2012/7-2012/3=4

and so on ......

 

(4)*** There are some special cases that do not appear in this sample data. For example, the highest prices appear in both 2011/7 and 2011/8, but I want to use 2011/8 (the most recent date). How to deal with this problem?

 

 

Obs PERMNO TIME high12month year month high
1 10001 0 . 2011 1 10.9200
2 10001 1 . 2011 2 10.9900
3 10001 2 . 2011 3 11.7300
4 10001 3 . 2011 4 11.8300
5 10001 4 . 2011 5 11.5000
6 10001 5 . 2011 6 11.6000
7 10001 6 . 2011 7 11.8699
8 10001 7 . 2011 8 11.5500
9 10001 8 . 2011 9 10.9900
10 10001 9 . 2011 10 11.0900
11 10001 10 . 2011 11 11.1000
12 10001 11 11.8699 2011 12 11.4200
13 10001 12 11.8699 2012 1 11.4400
14 10001 13 11.8699 2012 2 11.3499
15 10001 14 11.8699 2012 3 11.6600
16 10001 15 11.8699 2012 4 11.4899
17 10001 16 11.8699 2012 5 11.3400
18 10001 17 11.8699 2012 6 10.8000
19 10001 18 11.6600 2012 7 10.2700
20 10001 19 11.6600 2012 8 10.0900
21 10001 20 11.6600 2012 9 10.0400
22 10001 21 11.6600 2012 10 10.0700
23 10001 22 11.6600 2012 11 10.0300
24 10001 23 11.6600 2012 12 9.6400
25 10001 24 11.6600 2013 1 9.9500
26 10001 25 11.6600 2013 2 10.0299
27 10001 26 11.4899 2013 3 10.2000
28 10001 27 11.3400 2013 4 10.5000
29 10001 28 10.8000 2013 5 10.7000
30 10001 29 10.7000 2013 6 10.6700
31 10001 30 10.8200 2013 7 10.8200
32 10001 31 10.8200 2013 8 10.4600
33 10001 32 10.8200 2013 9 10.4300
34 10001 33 10.8200 2013 10 10.4500
35 10001 34 10.8200 2013 11 10.0400
36 10001 35 10.8200 2013 12 8.3200
37 10002 0 . 2011 1 2.9000
38 10002 1 . 2011 2 2.7000
39 10002 2 . 2011 3 2.7000
40 10002 3 . 2011 4 2.7400
41 10002 4 . 2011 5 2.5300
42 10002 5 . 2011 6 2.6700
43 10002 6 . 2011 7 2.5800
44 10002 7 . 2011 8 2.4000
45 10002 8 . 2011 9 2.4000
46 10002 9 . 2011 10 2.3500
47 10002 10 . 2011 11 1.7300
48 10002 11 2.9000 2011 12 1.6600
49 10002 12 2.7400 2012 1 1.4000
50 10002 13 2.7400 2012 2 1.4000
51 10002 14 2.7400 2012 3 1.4900
52 10002 15 2.6700 2012 4 2.1700
53 10002 16 2.9800 2012 5 2.9800
54 10002 17 2.9900 2012 6 2.9900
55 10002 18 3.2000 2012 7 3.2000
56 10002 19 3.2000 2012 8 3.0000
57 10002 20 3.2000 2012 9 3.1500
58 10002 21 3.2000 2012 10 3.0500
59 10002 22 3.2000 2012 11 2.9000
60 10002 23 3.2000 2012 12 2.8101
61 10002 24 3.2000 2013 1 2.9500
62 10002 25 3.2000 2013 2 2.9800

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎08-07-2016 10:46 AM
Super User
Posts: 9,867

Re: Months since 52-week high

That would not be hard.
Note: Post your data at here , No Attachment. Nobody would like to download it from website.


proc import datafile='/folders/myfolders/daily.xlsx' out=daily dbms=xlsx replace;run;
proc import datafile='/folders/myfolders/monthly.xlsx' out=monthly dbms=xlsx replace;run;

data daily;
 set daily;
 year=year(date);
 month=month(date);
run;
data monthly;
 set monthly;
 copy_year=year;
 copy_month=month;
 copy_high=high;
run;

data want;
 if _n_=1 then do;
  if 0 then set daily;
  declare hash h(dataset:'daily',hashexp:20,duplicate:'r');
  h.definekey('permno','year','month','price');
  h.definedata('date');
  h.definedone();
 end;
set monthly;
 n=0;
 if not missing(high12month) then do;
  do while(high12month ne high)	;
   n+1;p=_n_-n;
   set monthly(keep=year month high) point=p;
  end;
 
 call missing(date);
 price=high;
 rc=h.find();
 if rc=0 then SpecificDays=intnx('month',mdy(copy_month,1,copy_year),0,'e')-date;
 end;
 drop p rc price year month high;
run;

View solution in original post


All Replies
Super User
Posts: 19,155

Re: Months since 52-week high

Us an array to store previous month values and to determine how far away via the index. 

 

https://gist.github.com/statgeek/27e23c015eae7953eff2

 

Use max function to find max, whichn to find the index 

 

Super User
Posts: 9,867

Re: Months since 52-week high

OK. You didn't post that Gap/Months yet .
Assuming There are no gap between two monthes.


data have;
infile cards expandtabs truncover;
input Obs	PERMNO	TIME	high12month	 year	month	high ;
cards;
1	10001	0	.	2011	1	10.9200
2	10001	1	.	2011	2	10.9900
3	10001	2	.	2011	3	11.7300
4	10001	3	.	2011	4	11.8300
5	10001	4	.	2011	5	11.5000
6	10001	5	.	2011	6	11.6000
7	10001	6	.	2011	7	11.8699
8	10001	7	.	2011	8	11.5500
9	10001	8	.	2011	9	10.9900
10	10001	9	.	2011	10	11.0900
11	10001	10	.	2011	11	11.1000
12	10001	11	11.8699	2011	12	11.4200
13	10001	12	11.8699	2012	1	11.4400
14	10001	13	11.8699	2012	2	11.3499
15	10001	14	11.8699	2012	3	11.6600
16	10001	15	11.8699	2012	4	11.4899
17	10001	16	11.8699	2012	5	11.3400
18	10001	17	11.8699	2012	6	10.8000
19	10001	18	11.6600	2012	7	10.2700
20	10001	19	11.6600	2012	8	10.0900
21	10001	20	11.6600	2012	9	10.0400
22	10001	21	11.6600	2012	10	10.0700
23	10001	22	11.6600	2012	11	10.0300
24	10001	23	11.6600	2012	12	9.6400
25	10001	24	11.6600	2013	1	9.9500
26	10001	25	11.6600	2013	2	10.0299
27	10001	26	11.4899	2013	3	10.2000
28	10001	27	11.3400	2013	4	10.5000
29	10001	28	10.8000	2013	5	10.7000
30	10001	29	10.7000	2013	6	10.6700
31	10001	30	10.8200	2013	7	10.8200
32	10001	31	10.8200	2013	8	10.4600
33	10001	32	10.8200	2013	9	10.4300
34	10001	33	10.8200	2013	10	10.4500
35	10001	34	10.8200	2013	11	10.0400
36	10001	35	10.8200	2013	12	8.3200
37	10002	0	.	2011	1	2.9000
38	10002	1	.	2011	2	2.7000
39	10002	2	.	2011	3	2.7000
40	10002	3	.	2011	4	2.7400
41	10002	4	.	2011	5	2.5300
42	10002	5	.	2011	6	2.6700
43	10002	6	.	2011	7	2.5800
44	10002	7	.	2011	8	2.4000
45	10002	8	.	2011	9	2.4000
46	10002	9	.	2011	10	2.3500
47	10002	10	.	2011	11	1.7300
48	10002	11	2.9000	2011	12	1.6600
49	10002	12	2.7400	2012	1	1.4000
50	10002	13	2.7400	2012	2	1.4000
51	10002	14	2.7400	2012	3	1.4900
52	10002	15	2.6700	2012	4	2.1700
53	10002	16	2.9800	2012	5	2.9800
54	10002	17	2.9900	2012	6	2.9900
55	10002	18	3.2000	2012	7	3.2000
56	10002	19	3.2000	2012	8	3.0000
57	10002	20	3.2000	2012	9	3.1500
58	10002	21	3.2000	2012	10	3.0500
59	10002	22	3.2000	2012	11	2.9000
60	10002	23	3.2000	2012	12	2.8101
61	10002	24	3.2000	2013	1	2.9500
62	10002	25	3.2000	2013	2	2.9800
;
run;
data want;
 set have;
 if not missing(high12month) then do;
  n=0;
  do while(high12month ne high)	;
   n+1;p=_n_-n;
   set have(keep=high) point=p;
  end;
 end;
 drop p;
 run;


Super User
Posts: 9,867

Re: Months since 52-week high

Opps. I found a problem in my code. Try this one :

data have;
infile cards expandtabs truncover;
input Obs	PERMNO	TIME	high12month	 year	month	high ;
cards;
1	10001	0	.	2011	1	10.9200
2	10001	1	.	2011	2	10.9900
3	10001	2	.	2011	3	11.7300
4	10001	3	.	2011	4	11.8300
5	10001	4	.	2011	5	11.5000
6	10001	5	.	2011	6	11.6000
7	10001	6	.	2011	7	11.8699
8	10001	7	.	2011	8	11.8699
9	10001	8	.	2011	9	10.9900
10	10001	9	.	2011	10	11.0900
11	10001	10	.	2011	11	11.1000
12	10001	11	11.8699	2011	12	11.4200
13	10001	12	11.8699	2012	1	11.4400
14	10001	13	11.8699	2012	2	11.3499
15	10001	14	11.8699	2012	3	11.6600
16	10001	15	11.8699	2012	4	11.4899
17	10001	16	11.8699	2012	5	11.3400
18	10001	17	11.8699	2012	6	10.8000
19	10001	18	11.6600	2012	7	10.2700
20	10001	19	11.6600	2012	8	10.0900
21	10001	20	11.6600	2012	9	10.0400
22	10001	21	11.6600	2012	10	10.0700
23	10001	22	11.6600	2012	11	10.0300
24	10001	23	11.6600	2012	12	9.6400
25	10001	24	11.6600	2013	1	9.9500
26	10001	25	11.6600	2013	2	10.0299
27	10001	26	11.4899	2013	3	10.2000
28	10001	27	11.3400	2013	4	10.5000
29	10001	28	10.8000	2013	5	10.7000
30	10001	29	10.7000	2013	6	10.6700
31	10001	30	10.8200	2013	7	10.8200
32	10001	31	10.8200	2013	8	10.4600
33	10001	32	10.8200	2013	9	10.4300
34	10001	33	10.8200	2013	10	10.4500
35	10001	34	10.8200	2013	11	10.0400
36	10001	35	10.8200	2013	12	8.3200
37	10002	0	.	2011	1	2.9000
38	10002	1	.	2011	2	2.7000
39	10002	2	.	2011	3	2.7000
40	10002	3	.	2011	4	2.7400
41	10002	4	.	2011	5	2.5300
42	10002	5	.	2011	6	2.6700
43	10002	6	.	2011	7	2.5800
44	10002	7	.	2011	8	2.4000
45	10002	8	.	2011	9	2.4000
46	10002	9	.	2011	10	2.3500
47	10002	10	.	2011	11	1.7300
48	10002	11	2.9000	2011	12	1.6600
49	10002	12	2.7400	2012	1	1.4000
50	10002	13	2.7400	2012	2	1.4000
51	10002	14	2.7400	2012	3	1.4900
52	10002	15	2.6700	2012	4	2.1700
53	10002	16	2.9800	2012	5	2.9800
54	10002	17	2.9900	2012	6	2.9900
55	10002	18	3.2000	2012	7	3.2000
56	10002	19	3.2000	2012	8	3.0000
57	10002	20	3.2000	2012	9	3.1500
58	10002	21	3.2000	2012	10	3.0500
59	10002	22	3.2000	2012	11	2.9000
60	10002	23	3.2000	2012	12	2.8101
61	10002	24	3.2000	2013	1	2.9500
62	10002	25	3.2000	2013	2	2.9800
;
run;
data want;
 set have;
 n=0;
 if not missing(high12month) then do;
  do while(high12month ne high)	;
   n+1;p=_n_-n;
   set have(keep=high) point=p;
  end;
 end;
 drop p;
 run;



Contributor ZZB
Contributor
Posts: 34

Re: Months since 52-week high

Great!It works, though sas takes much time to run this code. Thanks, Keshan. But I have one more hard question now.

 

If we are given these 'Daily' and 'Monthly' data, it it possible to add a 'HighDate' variable so that I can calcuate the specific Days since 52-week high.

 

For example, for 2011/12, the most recent highest price during 2011/1-2011/12 appears on 07/08/2011. Now I want to attach '07/08/2011' in the line of 'Monthly data' date 2011/12, so that I can calcuate the specific days since the most recent 52-week high = 12/31/2011-07/08/2011.

In the Monthly dataset, the 2011/12 is assumed to 12/31/2011, the end of month. 

For 2012/1, the most recent highest price during 2011/2-2012/1 appears on 07/08/2011, days since 52-w high=1/31/2012-07/08/2011. and so on....

 

 

 

 

Solution
‎08-07-2016 10:46 AM
Super User
Posts: 9,867

Re: Months since 52-week high

That would not be hard.
Note: Post your data at here , No Attachment. Nobody would like to download it from website.


proc import datafile='/folders/myfolders/daily.xlsx' out=daily dbms=xlsx replace;run;
proc import datafile='/folders/myfolders/monthly.xlsx' out=monthly dbms=xlsx replace;run;

data daily;
 set daily;
 year=year(date);
 month=month(date);
run;
data monthly;
 set monthly;
 copy_year=year;
 copy_month=month;
 copy_high=high;
run;

data want;
 if _n_=1 then do;
  if 0 then set daily;
  declare hash h(dataset:'daily',hashexp:20,duplicate:'r');
  h.definekey('permno','year','month','price');
  h.definedata('date');
  h.definedone();
 end;
set monthly;
 n=0;
 if not missing(high12month) then do;
  do while(high12month ne high)	;
   n+1;p=_n_-n;
   set monthly(keep=year month high) point=p;
  end;
 
 call missing(date);
 price=high;
 rc=h.find();
 if rc=0 then SpecificDays=intnx('month',mdy(copy_month,1,copy_year),0,'e')-date;
 end;
 drop p rc price year month high;
run;

Contributor ZZB
Contributor
Posts: 34

Re: Months since 52-week high

Monthly data

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 687 views
  • 3 likes
  • 3 in conversation