topic Re: pick data without the foward data in SAS Procedures

pick data without the foward data

bqk — Sun, 15 May 2011 11:51:27 GMT

i have 2 data set as following, i want to make it combained like data3.

ex i want to pick the data optionid is 21266383.
however, i don not want to get the data on 2004/1/2
i just want the data between 2004/1 /5~2004/1/12

please help me thanks~

data data1;
input date: yymmdd10. optionid exdate:yymmdd10. strike_price;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 20752179 2004/6/19 995
2004/1/5 21266383 2004/2/21 1010
2004/1/7 21273951 2004/1/17 1015
;
run;

data data2;
input date: yymmdd10. SPXClose optionid exdate:yymmdd10. best_bid bes_offer;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 1108.48 20752179 2004/6/19 15.5 17.1
2004/1/2 1108.48 21266383 2004/2/21 0 4.5
2004/1/2 1108.48 21273951 2004/1/17 0 1
2004/1/5 1122.22 20752179 2004/6/19 13.4 15
2004/1/5 1122.22 21266383 2004/2/21 5 3.1
2004/1/5 1122.22 21273951 2004/1/17 1250 0.4
2004/1/6 1123.67 20752179 2004/6/19 11.4 13
2004/1/6 1123.67 21266383 2004/2/21 5 2.6
2004/1/6 1123.67 21273951 2004/1/17 1380 0.1
2004/1/7 1126.33 20752179 2004/6/19 10.3 11.9
2004/1/7 1126.33 21266383 2004/2/21 9 2
2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
2004/1/8 1131.92 21266383 2004/2/21 19 2
2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
2004/1/9 1121.86 21266383 2004/2/21 21 2.7
2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
2004/1/12 1127.23 21266383 2004/2/21 21 2.05
2004/1/12 1127.23 21273951 2004/1/17 1480 0.1
;
run;

data data3;
input date: yymmdd10. SPXClose optionid exdate:yymmdd10. best_bid bes_offer;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 1108.48 20752179 2004/6/19 15.5 17.1
2004/1/5 1122.22 20752179 2004/6/19 13.4 15
2004/1/6 1123.67 20752179 2004/6/19 11.4 13
2004/1/7 1126.33 20752179 2004/6/19 10.3 11.9
2004/1/5 1122.22 21266383 2004/2/21 5 3.1
2004/1/6 1123.67 21266383 2004/2/21 5 2.6
2004/1/7 1126.33 21266383 2004/2/21 9 2
2004/1/8 1131.92 21266383 2004/2/21 19 2
2004/1/9 1121.86 21266383 2004/2/21 21 2.7
2004/1/12 1127.23 21266383 2004/2/21 21 2.05
2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
2004/1/12 1127.23 21273951 2004/1/17 1480 0.1
;
run;

Re: pick data without the foward data

art297 — Sun, 15 May 2011 14:17:28 GMT

Does the following do what you want?
[pre]
proc sql noprint;
create table data3 as
select b.*
from data1 as a, data2 as b
where a.optionid eq b.optionid and
b.date between a.date and a.exdate
order by b.optionid,b.date
;
quit;
[/pre]
Art
--------
> i have 2 data set as following, i want to make it
> combained like data3.
>
> ex i want to pick the data optionid is 21266383.
> however, i don not want to get the data on
> 2004/1/2
> i just want the data between 2004/1 /5~2004/1/12
> lease help me thanks~
>
> data data1;
> input date:
> yymmdd10. optionid exdate:yymmdd10. strike_price;
> format date yymmdd10. exdate:yymmdd10. ;
> datalines;
> 2004/1/2 20752179 2004/6/19 995
> 2004/1/5 21266383 2004/2/21 1010
> 2004/1/7 21273951 2004/1/17 1015
> ;
> run;
>
> data data2;
> input date: yymmdd10. SPXClose
> optionid exdate:yymmdd10. best_bid bes_offer;
> format date yymmdd10. exdate:yymmdd10. ;
> datalines;
> 2004/1/2 1108.48 20752179 2004/6/19 15.5 17.1
> 2004/1/2 1108.48 21266383 2004/2/21 0 4.5
> 2004/1/2 1108.48 21273951 2004/1/17 0 1
> 2004/1/5 1122.22 20752179 2004/6/19 13.4 15
> 2004/1/5 1122.22 21266383 2004/2/21 5 3.1
> 2004/1/5 1122.22 21273951 2004/1/17 1250 0.4
> 2004/1/6 1123.67 20752179 2004/6/19 11.4 13
> 2004/1/6 1123.67 21266383 2004/2/21 5 2.6
> 2004/1/6 1123.67 21273951 2004/1/17 1380 0.1
> 2004/1/7 1126.33 20752179 2004/6/19 10.3 11.9
> 2004/1/7 1126.33 21266383 2004/2/21 9 2
> 2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
> 2004/1/8 1131.92 21266383 2004/2/21 19 2
> 2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
> 2004/1/9 1121.86 21266383 2004/2/21 21 2.7
> 2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
> 2004/1/12 1127.23 21266383 2004/2/21 21 2.05
> 2004/1/12 1127.23 21273951 2004/1/17 1480 0.1
> ;
> run;
>
> data data3;
> input date: yymmdd10. SPXClose
> optionid exdate:yymmdd10. best_bid bes_offer;
> format date yymmdd10. exdate:yymmdd10. ;
> datalines;
> 2004/1/2 1108.48 20752179 2004/6/19 15.5 17.1
> 2004/1/5 1122.22 20752179 2004/6/19 13.4 15
> 2004/1/6 1123.67 20752179 2004/6/19 11.4 13
> 2004/1/7 1126.33 20752179 2004/6/19 10.3 11.9
> 2004/1/5 1122.22 21266383 2004/2/21 5 3.1
> 2004/1/6 1123.67 21266383 2004/2/21 5 2.6
> 2004/1/7 1126.33 21266383 2004/2/21 9 2
> 2004/1/8 1131.92 21266383 2004/2/21 19 2
> 2004/1/9 1121.86 21266383 2004/2/21 21 2.7
> 2004/1/12 1127.23 21266383 2004/2/21 21 2.05
> 2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
> 2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
> 2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
> 2004/1/12 1127.23 21273951 2004/1/17 1480 0.1
> ;
> run;

Re: pick data without the foward data

bqk — Sun, 15 May 2011 22:51:04 GMT

Thanks for Art's help

however, could i ask an advanced question?

the data1 is changed and i want to the result like data3

for instance, the optionid which is 20752179 change to 23752179

because its starting date is the first, i want the 23752179 group is the first.

data data1;
input date: yymmdd10. optionid exdate:yymmdd10. strike_price;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 23752179 2004/6/19 995
2004/1/5 21266383 2004/2/21 1010
2004/1/7 21273951 2004/1/17 1015

;
run;

data data2;
input date: yymmdd10. SPXClose optionid exdate:yymmdd10. best_bid bes_offer;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 1108.48 23752179 2004/6/19 15.5 17.1
2004/1/2 1108.48 21266383 2004/2/21 0 4.5
2004/1/2 1108.48 21273951 2004/1/17 0 1
2004/1/5 1122.22 23752179 2004/6/19 13.4 15
2004/1/5 1122.22 21266383 2004/2/21 5 3.1
2004/1/5 1122.22 21273951 2004/1/17 1250 0.4
2004/1/6 1123.67 23752179 2004/6/19 11.4 13
2004/1/6 1123.67 21266383 2004/2/21 5 2.6
2004/1/6 1123.67 21273951 2004/1/17 1380 0.1
2004/1/7 1126.33 23752179 2004/6/19 10.3 11.9
2004/1/7 1126.33 21266383 2004/2/21 9 2
2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
2004/1/8 1131.92 21266383 2004/2/21 19 2
2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
2004/1/9 1121.86 21266383 2004/2/21 21 2.7
2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
2004/1/12 1127.23 21266383 2004/2/21 21 2.05
2004/1/12 1127.23 21273951 2004/1/17 1480 0.1

;
run;

data data3;
input date: yymmdd10. SPXClose optionid exdate:yymmdd10. best_bid bes_offer;
format date yymmdd10. exdate:yymmdd10. ;
datalines;
2004/1/2 1108.48 23752179 2004/6/19 15.5 17.1
2004/1/5 1122.22 23752179 2004/6/19 13.4 15
2004/1/6 1123.67 23752179 2004/6/19 11.4 13
2004/1/7 1126.33 23752179 2004/6/19 10.3 11.9
2004/1/5 1122.22 21266383 2004/2/21 5 3.1
2004/1/6 1123.67 21266383 2004/2/21 5 2.6
2004/1/7 1126.33 21266383 2004/2/21 9 2
2004/1/8 1131.92 21266383 2004/2/21 19 2
2004/1/9 1121.86 21266383 2004/2/21 21 2.7
2004/1/12 1127.23 21266383 2004/2/21 21 2.05
2004/1/7 1126.33 21273951 2004/1/17 1391 0.25
2004/1/8 1131.92 21273951 2004/1/17 1391 0.05
2004/1/9 1121.86 21273951 2004/1/17 1391 0.05
2004/1/12 1127.23 21273951 2004/1/17 1480 0.1
;
run; Message was edited by: bqk

Re: pick data without the foward data

art297 — Sun, 15 May 2011 23:55:43 GMT

I think you are only asking how to do:
[pre]
proc sql noprint;
create table data3 as
select b.*,min(b.date) as startdate
from data1 as a, data2 as b
where a.optionid eq b.optionid and
b.date between a.date and a.exdate
group by b.optionid
order by calculated startdate,b.date
;
quit;
[/pre]
Art