BookmarkSubscribeRSS Feed
bqk
Calcite | Level 5 bqk
Calcite | Level 5
i have a data1 like following.

i want to do following step

1. see the last day in the different optionid group.
if the OTM > 0.1 then output dataA
else output dataB

2. in dataA and dataB,
the hold =absolute value of the delta

3. , the AAA is the hold -lag(hold) at the first day in the optionid group
in dataA, the AAA is AAA+strike_price
in dataB, the AAA is remained.

please help me thanks~~

the result is as following

data data1;
input date: yymmdd10. optionid SPXClose strike_price delta OTM ;
format date yymmdd10. ;
datalines;
2004/1/2 21213832 1108.48 995 -0.030962 0.002374423
2004/1/5 21213832 1122.22 995 -0.015296 0.013364581
2004/1/6 21213832 1123.67 995 -0.013465 0.014508708
2004/1/7 21213832 1126.33 995 -0.009658 0.016599931
2004/1/8 21213832 1131.92 995 -0.009319 0.020962612
2004/1/9 21213832 1121.86 995 -0.008953 0.013080064
2004/1/12 21213832 1127.23 995 -0.004376 0.017305253
2004/1/13 21213832 1121.22 995 -0.005754 0.012573804
2004/1/14 21213832 1130.52 995 0 0.01987404
2004/1/15 21213832 1132.05 995 0 0.021063557
2008/1/8 21238884 1390.19 1340 -0.180018 0.063897021
2008/1/9 21238884 1409.13 1340 -0.099134 0.050941361
2008/1/10 21238884 1420.33 1340 -0.067354 0.043442721
2008/1/11 21238884 1401.02 1340 -0.063845 0.056446018
2008/1/14 21238884 1416.25 1340 -0.026838 0.046160635
2008/1/15 21238884 1380.95 1340 -0.101002 0.070346501
2008/1/16 21238884 1373.2 1340 -0.126768 0.075822895
2008/1/17 21238884 1333.25 1340 -1 0.105062816

;
run;

data dataA;
input date: yymmdd10. optionid SPXClose strike_price delta OTM hold AAA;
format date yymmdd10. ;
datalines;
2004/1/2 21213832 1108.48 995 -0.030962 0.002374423 0.030962 0.030962
2004/1/5 21213832 1122.22 995 -0.015296 0.013364581 0.015296 -0.015666
2004/1/6 21213832 1123.67 995 -0.013465 0.014508708 0.013465 -0.001831
2004/1/7 21213832 1126.33 995 -0.009658 0.016599931 0.009658 -0.003807
2004/1/8 21213832 1131.92 995 -0.009319 0.020962612 0.009319 -0.000339
2004/1/9 21213832 1121.86 995 -0.008953 0.013080064 0.008953 -0.000366
2004/1/12 21213832 1127.23 995 -0.004376 0.017305253 0.004376 -0.004577
2004/1/13 21213832 1121.22 995 -0.005754 0.012573804 0.005754 0.001378
2004/1/14 21213832 1130.52 995 0 0.01987404 0 -0.005754
2004/1/15 21213832 1132.05 995 0 0.021063557 0 995

;
run;

data dataB;
input date: yymmdd10. optionid SPXClose strike_price delta OTM hold AAA;
format date yymmdd10. ;
datalines;
2008/1/8 21238884 1390.19 1340 -0.180018 0.063897021 0.180018 0.180018
2008/1/9 21238884 1409.13 1340 -0.099134 0.050941361 0.099134 -0.080884
2008/1/10 21238884 1420.33 1340 -0.067354 0.043442721 0.067354 -0.03178
2008/1/11 21238884 1401.02 1340 -0.063845 0.056446018 0.063845 -0.003509
2008/1/14 21238884 1416.25 1340 -0.026838 0.046160635 0.026838 -0.037007
2008/1/15 21238884 1380.95 1340 -0.101002 0.070346501 0.101002 0.074164
2008/1/16 21238884 1373.2 1340 -0.126768 0.075822895 0.126768 0.025766
2008/1/17 21238884 1333.25 1340 -1 0.105062816 1 0.873232

;
run;
2 REPLIES 2
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello Bqk,

If I understand you corretly this is a solution:
[pre]
proc sort data=data1;
by optionid descending date;
run;
data dataA dataB;
retain out;
set data1;
if FIRST.optionid and OTM GT 0.1 then out="A";
else if FIRST.optionid and OTM LE 0.1 then out="B";
if out="A" then output dataA;
if out="B" then output dataB;
by optionid;
drop out;
run;
proc sort data=dataA;
by optionid date;
run;
data ra;
retain lg;
set dataA;
hold=ABS(delta);
lg=LAG(hold);
if FIRST.optionid then do; AAA=hold; end;
else do; AAA=hold-lg; end;
by optionid;
drop lg;
run;
proc sort data=dataB;
by optionid date;
run;
data rb;
retain lg;
set dataB;
hold=ABS(delta);
lg=LAG(hold);
if FIRST.optionid then do; AAA=hold; end;
else do; AAA=hold-lg; end;
if LAST.optionid then AAA=AAA+strike_price;
by optionid;
drop lg;
run;
[/pre]
I should menthion than your condition
"1. see the last day in the different optionid group.
if the OTM > 0.1 then output dataA else output dataB"
contradicts with the contents of your dataA and dataB.
Also, it looks like your the "3. AAA is AAA+strike_price" applies to the last date for the current optionid.

Sincerely,
SPR
bqk
Calcite | Level 5 bqk
Calcite | Level 5
i think it is wrong that i type the dataA and dataB

it is true that if OTM>0.1 then output dataB else output dataA.

thank SPR much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 815 views
  • 0 likes
  • 2 in conversation