topic about the question of sort data in SAS Procedures

about the question of sort data

bqk — Wed, 13 Apr 2011 23:56:52 GMT

i want to merge figure1. and 2 to figure 3.
the data is complicate
you can see here
http://min.us/mvoikgu#1
http://min.us/mvoikgu#2
http://min.us/mvoikgu#3

data is as following
figure1.

Code CVX0504(PS) CVX0604(PS) CVX0704(PS)
2004/5/12 18.2 19.61 #NA
2004/5/13 18.38 19.58 #NA
2004/5/14 18.55 19.67 #NA
2004/5/17 19.37 20.21 #NA
2004/5/18 19.25 20.07 #NA
2004/5/19 18.35 19.17 #NA
2004/5/20 #NA 19.34 #NA
2004/5/21 #NA 19.17 #NA
2004/5/24 #NA 18.77 19.04
2004/5/25 #NA 17.5 18
2004/5/26 #NA 17.61 18.09
2004/5/27 #NA 17.35 17.9

figure 2.
Type STDT
CVX0504 2004/5/19
CVX0604 2004/6/16
CVX0704 2004/7/14

figure3.
date code exdate settle_price
2004/5/12 CVX0504 2004/5/19 18.2
2004/5/12 CVX0604 2004/6/16 19.61
2004/5/13 CVX0504 2004/5/19 18.38
2004/5/13 CVX0604 2004/6/16 19.58
2004/5/14 CVX0504 2004/5/19 18.55
2004/5/14 CVX0604 2004/6/16 19.67
2004/5/17 CVX0504 2004/5/19 19.37
2004/5/17 CVX0604 2004/6/16 20.21
2004/5/18 CVX0504 2004/5/19 19.25
2004/5/18 CVX0604 2004/6/16 20.07
2004/5/19 CVX0504 2004/5/19 18.35
2004/5/19 CVX0604 2004/6/16 19.17
2004/5/20 CVX0604 2004/6/16 19.34
2004/5/21 CVX0604 2004/6/16 19.17
2004/5/24 CVX0604 2004/6/16 18.77
2004/5/24 CVX0704 2004/7/14 19.04
2004/5/25 CVX0604 2004/6/16 17.5
2004/5/25 CVX0704 2004/7/14 18
2004/5/26 CVX0604 2004/6/16 17.61
2004/5/26 CVX0704 2004/7/14 18.09
2004/5/27 CVX0604 2004/6/16 17.35
2004/5/27 CVX0704 2004/7/14 17.9

in detail
i want a data there are settle price of various expiration date daily

please help me, thx

Message was edited by: bqk Message was edited by: bqk

Re: about the question of sort data

Ksharp — Thu, 14 Apr 2011 07:57:30 GMT

Can you plz post these data into the forum afterwards?
It spend lots of time to input data.
[pre]
data temp;
input date : yymmdd10. cvx0504 cvx0604 cvx0704 ;
format date yymmdd10.;
datalines;
2004/5/12 18.2 19.61 .
2004/5/13 18.38 19.58 .
2004/5/14 18.55 19.67 .
2004/5/17 19.37 20.21 .
2004/5/18 19.25 20.07 .
2004/5/19 18.35 19.17 .
2004/5/20 . 19.34 .
2004/5/21 . 19.17 .
2004/5/24 . 18.77 19.04
2004/5/25 . 17.5 18
2004/5/26 . 17.61 18.09
2004/5/27 . 17.35 17.9
;
run;
data index;
input code $ exdate : yymmdd10.;
format exdate yymmdd10.;
datalines;
cvx0504 2004/5/19
cvx0604 2004/6/16
cvx0704 2004/7/14
;
run;
data want;
set temp;
length code $ 8;
array cv{*} cvx:;
do i=1 to dim(cv);
if not missing(cv{i}) then do;
code=vname(cv{i});
settle_price=cv{i};
output;
end;
end;
keep date code settle_price;
run;
data want(drop=rc);
declare hash hh(hashexp:10);
hh.definekey('code');
hh.definedata('exdate');
hh.definedone();

do until(last);
set index end=last;
hh.add();
end;

do until(_last);
set want end=_last;
rc=hh.find();
output;
end;
stop;
run;
[/pre]

Ksharp Message was edited by: Ksharp

Re: about the question of sort data

bqk — Thu, 14 Apr 2011 12:43:31 GMT

thanks
the way you use is fast, correct, and flexible
thank you so much