the first and last in a group

bqk — Tue, 17 May 2011 11:30:47 GMT

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;

Re: the first and last in a group

SPR — Tue, 17 May 2011 14:10:15 GMT

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

Re: the first and last in a group

bqk — Tue, 17 May 2011 16:07:58 GMT

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

topic the first and last in a group in SAS Procedures

the first and last in a group

Re: the first and last in a group

Re: the first and last in a group