topic Re: pick data if price higher than previous price in SAS Procedures

pick data if price higher than previous price

RemusWayne — Mon, 16 May 2011 21:20:39 GMT

I will face one of the four situation below every trading day.
1. Keep the old contract and no need to buy a new contract.
2. Sell the old contract ,and buy a new contract with a new strike price.
3. When exdate minus date equal seven, I sell the old contract and buy a new contract with the same strike price.
4. Same as three, but the nearest At-The-Money has the different strike price ,so I sell the old contract and buy a new one with the new strike price.

How can I combine the four situations into one code?

Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/2 2008/1/16 C 200 27.0 23.15
2008/1/2 2008/1/16 P 300 27.0 23.15
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 756 25.0 23.42
2008/1/3 2008/1/16 P 654 25.0 23.42
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 276 22.5 27.00
2008/1/4 2008/1/16 P 246 22.5 27.00
2008/1/4 2008/1/16 C 276 25.0 27.00
2008/1/4 2008/1/16 P 221 25.0 27.00
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 222 22.5 24.00
2008/1/5 2008/1/16 P 343 22.5 24.00
2008/1/5 2008/1/16 C 768 25.0 24.00
2008/1/5 2008/1/16 P 343 25.0 24.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
.
.
.
data for question 3
2008/1/9 2008/1/16 C 393 27.5 24.10
2008/1/9 2008/1/16 P 493 27.5 24.10
2008/1/9 2008/2/15 C 293 25.5 24.10
2008/1/9 2008/2/15 P 693 25.5 24.10
2008/1/9 2008/2/15 C 293 27.5 24.10
2008/1/9 2008/2/15 P 593 27.5 24.10

data for question 4
2008/1/9 2008/1/16 C 393 27.5 29.10
2008/1/9 2008/1/16 P 493 27.5 29.10
2008/1/9 2008/2/15 C 293 27.5 29.10
2008/1/9 2008/2/15 P 693 27.5 29.10
2008/1/9 2008/2/15 C 293 30.0 29.10
2008/1/9 2008/2/15 P 593 30.0 29.10

According to my previous question, i select contract when min{ABS(strike_price-settle_price)}.

Question1: At 1/4 i should choice the contract which has strike_price for 27.5.
The MIN{ABS()} only 0.5. I also want keep the contract that has strike_price for
22.5 at 1/4. When I buy new contract I should sell old contract at 1/4.
Can I keep the contract that has old strike_price and old exdate every time when i buy new contract.

Question2:
If settle price not bigger than previous maximum strike_price like 1/5, i just keep the contract which has old strike_price and old exdate at new date.
At 1/5 i just retain the data

2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00

Question3:
If the date is seven days before Exdate.(date=2008/1/9 exdate=2008/1/16)
No matter settle_price bigger than previous maximum strike_price.
I need to change the contract that i hold to the new contract which has new exdate at next month. At 1/9 i want
the data like example below

2008/1/9 2008/1/16 C 393 27.5 24.10
2008/1/9 2008/1/16 P 493 27.5 24.10
2008/1/9 2008/2/15 C 293 27.5 24.10
2008/1/9 2008/2/15 P 593 27.5 24.10

Question(4)
If settle_price > previous maximum strike_price at 1/9
We buy the new contract that has new strike_price with exdate an next month.
I want the data as follows.

2008/1/9 2008/1/16 C 393 27.5 29.10
2008/1/9 2008/1/16 P 493 27.5 29.10
2008/1/9 2008/2/15 C 293 30.0 29.10
2008/1/9 2008/2/15 P 593 30.0 29.10

I know this question is very complex.
But plz help. Thank you very very very very much.

To SAS HERO Message was edited by: RemusWayne

Re: pick data if price higher than previous price

Ksharp — Wed, 18 May 2011 06:39:21 GMT

OK. I hope I am that SAS HERO. 🙂
But for your Question 3 is ambiguous,I am not understand totally.
Question one:
[pre]
Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/2 2008/1/16 C 200 27.0 23.15
2008/1/2 2008/1/16 P 300 27.0 23.15
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 756 25.0 23.42
2008/1/3 2008/1/16 P 654 25.0 23.42
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 276 22.5 27.00
2008/1/4 2008/1/16 P 246 22.5 27.00
2008/1/4 2008/1/16 C 276 25.0 27.00
2008/1/4 2008/1/16 P 221 25.0 27.00
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 222 22.5 24.00
2008/1/5 2008/1/16 P 343 22.5 24.00
2008/1/5 2008/1/16 C 768 25.0 24.00
2008/1/5 2008/1/16 P 343 25.0 24.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
;
run;
proc sql;
create table want as
select *
from data1
group by date
having abs(Strike_Price-Settle_Price) =
min(abs(Strike_Price-Settle_Price))
;
quit;
proc sort data=want nodupkey out=op1(keep=date strike_price);
by date strike_price;
run;
data op1;
set op1;
_strike_price=lag(strike_price);
drop strike_price;
run;
data temp(where=(strike_price=_strike_price));
merge data1 op1;
by date;
;

run;
data want;
set want temp(drop=_strike_price);
by date;
run;
[/pre]

Question Two:

[pre]

Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/2 2008/1/16 C 200 27.0 23.15
2008/1/2 2008/1/16 P 300 27.0 23.15
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 756 25.0 23.42
2008/1/3 2008/1/16 P 654 25.0 23.42
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 276 22.5 27.00
2008/1/4 2008/1/16 P 246 22.5 27.00
2008/1/4 2008/1/16 C 276 25.0 27.00
2008/1/4 2008/1/16 P 221 25.0 27.00
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 222 22.5 24.00
2008/1/5 2008/1/16 P 343 22.5 24.00
2008/1/5 2008/1/16 C 768 25.0 24.00
2008/1/5 2008/1/16 P 343 25.0 24.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
;
run;
proc sql;
create table temp as
select *,max(strike_price) as max_strike
from data1
group by date
having abs(Strike_Price-Settle_Price) =
min(abs(Strike_Price-Settle_Price))
;
quit;
proc sort data=temp nodupkey out=op(keep=date max_strike Strike_Price);
by date max_strike;
run;
data op;
set op;
lag_max_strike=lag(max_strike);
_Strike_Price=lag(Strike_Price);
drop max_strike Strike_Price;
run;

data want ;
merge data1 op;
by date;
if Settle_Price le lag_max_strike and _Strike_Price=Strike_Price
then output;
run;
[/pre]

Question Three:

[pre]

Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/10 2008/1/16 C 393 27.5 24.10
2008/1/10 2008/1/16 P 493 27.5 24.10
2008/1/10 2008/2/15 C 293 25.5 24.10
2008/1/10 2008/2/15 P 693 25.5 24.10
2008/1/10 2008/2/15 C 293 27.5 24.10
2008/1/10 2008/2/15 P 593 27.5 24.10
;
run;
proc sql;
create table temp as
select *
from data1
where exdate-date ge 5
group by date,exdate
having abs(Strike_Price-Settle_Price) =
min(abs(Strike_Price-Settle_Price))
;
quit;
proc sort data=temp nodupkey out=op(keep=date exdate strike_price);
by date exdate;
run;
data op;
set op;
_strike_price=lag(strike_price);
if _n_ =1 then _strike_price=strike_price;
drop strike_price;
run;
data want;
merge data1 op;
by date exdate;
if _strike_price=strike_price;
drop _strike_price;
run;
[/pre]

Question Four:

[pre]

Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/10 2008/1/16 C 393 27.5 29.10
2008/1/10 2008/1/16 P 493 27.5 29.10
2008/1/10 2008/2/15 C 293 27.5 29.10
2008/1/10 2008/2/15 P 693 27.5 29.10
2008/1/10 2008/2/15 C 293 30.0 29.10
2008/1/10 2008/2/15 P 593 30.0 29.10
;
run;
proc sql;
create table temp as
select *,max(strike_price) as max_strike
from data1
group by date,exdate
having abs(Strike_Price-Settle_Price) =
min(abs(Strike_Price-Settle_Price))
;
quit;
proc sort data=temp nodupkey out=op(keep=date exdate max_strike );
by date exdate max_strike;
run;
data op;
set op;
lag_max_strike=lag(max_strike);
drop max_strike ;
run;
data want;
merge data1 op;
by date exdate;
if strike_price ne lag_max_strike;
drop lag_max_strike;
run;
[/pre]

Ksharp

Re: pick data if price higher than previous price

RemusWayne — Wed, 18 May 2011 08:14:47 GMT

Yes , you are my SAS hero. THANK YOU !!

Sorry to say that I have posted some wrong data here.
I've re-edited it above.

I've learned a lot from your reply and they are very much helping.
However, I still have some confusing questions.

The date we have here are continuous running throughout the year.
So, I was wondering if SAS can combine the four situation into one table for me to check everyday's data.

RemusWayne

Re: pick data if price higher than previous price

Ksharp — Wed, 18 May 2011 10:48:20 GMT

I think answer is yes.But need more time to consider these situation and think how to code it If you want.

Ksharp

Re: pick data if price higher than previous price

RemusWayne — Wed, 18 May 2011 12:43:34 GMT

2008/1/2 2008/1/16 C 200 27.0 23.15
2008/1/2 2008/1/16 P 300 27.0 23.15
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 756 25.0 23.42
2008/1/3 2008/1/16 P 654 25.0 23.42
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 276 22.5 27.00
2008/1/4 2008/1/16 P 246 22.5 27.00
2008/1/4 2008/1/16 C 276 25.0 27.00
2008/1/4 2008/1/16 P 221 25.0 27.00
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 222 22.5 24.00
2008/1/5 2008/1/16 P 343 22.5 24.00
2008/1/5 2008/1/16 C 768 25.0 24.00
2008/1/5 2008/1/16 P 343 25.0 24.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
2008/1/6 2008/1/16 C 234 25.0 24.50
2008/1/6 2008/1/16 P 235 25.0 24.50
2008/1/6 2008/1/16 C 345 27.5 24.50
2008/1/6 2008/1/16 P 368 27.5 24.50

I have modified Question1

I want to lock in the high strike price of my database
At 1/5 the settle price < previous maximum strike price(27.5)
I want to keep the contract with the maximum strike price but not
at-the-money at 1/5
The following data is what I want to keep
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
2008/1/6 2008/1/16 C 345 27.5 24.50
2008/1/6 2008/1/16 P 368 27.5 24.50

.
.
.

Re: pick data if price higher than previous price

Ksharp — Thu, 19 May 2011 01:44:05 GMT

OK.
[pre]

Data data1;
input Date : yymmdd10. Exdate : yymmdd10. CP_FLAG $ Volum Strike_Price Settle_Price ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2008/1/2 2008/1/16 C 200 27.0 23.15
2008/1/2 2008/1/16 P 300 27.0 23.15
2008/1/2 2008/1/16 C 552 22.5 23.15
2008/1/2 2008/1/16 P 561 22.5 23.15
2008/1/3 2008/1/16 C 756 25.0 23.42
2008/1/3 2008/1/16 P 654 25.0 23.42
2008/1/3 2008/1/16 C 783 22.5 23.42
2008/1/3 2008/1/16 P 150 22.5 23.42
2008/1/4 2008/1/16 C 276 22.5 27.00
2008/1/4 2008/1/16 P 246 22.5 27.00
2008/1/4 2008/1/16 C 276 25.0 27.00
2008/1/4 2008/1/16 P 221 25.0 27.00
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00
2008/1/5 2008/1/16 C 222 22.5 24.00
2008/1/5 2008/1/16 P 343 22.5 24.00
2008/1/5 2008/1/16 C 768 25.0 24.00
2008/1/5 2008/1/16 P 343 25.0 24.00
2008/1/5 2008/1/16 C 213 27.5 24.00
2008/1/5 2008/1/16 P 343 27.5 24.00
2008/1/6 2008/1/16 C 234 25.0 24.50
2008/1/6 2008/1/16 P 235 25.0 24.50
2008/1/6 2008/1/16 C 345 27.5 24.50
2008/1/6 2008/1/16 P 368 27.5 24.50
;
run;
proc sql;
create table temp as
select *,max(strike_price) as max_strike
from data1
group by date
having abs(Strike_Price-Settle_Price) =
min(abs(Strike_Price-Settle_Price))
;
quit;
proc sort data=temp nodupkey out=op(keep=date max_strike Strike_Price);
by date max_strike;
run;
data op;
set op;
lag_max_strike=lag(max_strike);
_Strike_Price=lag(Strike_Price);
drop max_strike Strike_Price;
run;

data _want ;
merge data1 op;
by date;
if Settle_Price le lag_max_strike ;
drop lag_max_strike _Strike_Price;
run;
proc sql;
create table want as
select *
from _want
group by date
having Strike_Price=max(Strike_Price)
;
quit;
[/pre]

Ksharp

Re: pick data if price higher than previous price

RemusWayne — Thu, 19 May 2011 03:02:53 GMT

Thank you very much.

I want to lock in the high strike price of my database
At 1/5 the settle price < previous maximum strike price(27.5)
I want to keep the contract with the" maximum strike price" but not
at-the-money at 1/5

maximum strike price ==> I redefined it= The closest contract's strike price

At 1/5 the closest contract is contract with strike price 27.5 at 1/4

At 1/6 the closest contract is contract with strike price 27.5 at 1/4

I Follow your code but the answer lack of data at 1/4.
2008/1/4 2008/1/16 C 344 27.5 27.00
2008/1/4 2008/1/16 P 476 27.5 27.00

Re: pick data if price higher than previous price

Ksharp — Thu, 19 May 2011 09:58:27 GMT

I am confused totally. It would be better to show what your output looks like.
if you only want the highest strike price, no matter with the settle price < previous maximum strike price.Only one sql statement can get it.
[pre]
proc sql;
create table want as
select *
from data1
group by date
having Strike_Price=max(Strike_Price)
;
quit;

Ksharp

Message was edited by: Ksharp

Message was edited by: Ksharp Message was edited by: Ksharp