|
Ashok,
As Richard mentioned, the full task is definitely NOT trivial.
I think that the following provides the output you want, but only for the
first circular trading that it finds. That is, if the first buyer (a) sells
half of the stock to b and the other half to c, only the first track will be
traced.
I don't know if a hash solution can simplify that part of the task.
Art
The code:
data have;
infile datalines;
input symbol seller_code $ buyer_code $ price quantity datetime ;
informat datetime anydtdtm17.;
date=datepart(datetime);
time=timepart(datetime);
format date ddmmyy8.;
format time time8.;
datalines;
100 a b 100 100 15OCT2008:15:30:00
100 d e 150 90 15OCT2008:10:45:50
100 b c 200 100 15OCT2008:15:30:00
100 e f 220 100 15OCT2008:15:30:00
100 f g 180 100 15OCT2008:15:30:00
100 c b 160 100 15OCT2008:15:30:00
100 l m 300 100 15OCT2008:15:30:00
100 p q 350 100 15OCT2008:15:30:00
100 m n 400 90 15OCT2008:15:30:00
100 n o 450 90 15OCT2008:15:30:00
100 o l 500 90 15OCT2008:15:30:00
;
proc sort data=have;
by symbol;
run;
proc transpose data=have out=transposed;
var seller_code buyer_code price quantity date time datetime;
by symbol;
run;
data want (keep=symbol Normal_Path price quantity date
time datetime Group_Circular_Path);
set transposed;
array indata(*) $ col:;
array keepdata(7,32) $20;
array Sequence(32);
retain keepdata;
format Group_Circular_Path $200.;
if mod(_n_,7) then do i=1 to dim(indata);
keepdata(mod(_n_,7),i)=indata(i);
end;
else do;
do i=1 to dim(indata);
keepdata(7,i)=indata(i);
end;
do i=1 to dim(indata);
Group_Circular_Path=catx('-',keepdata(1,i),keepdata(2,i));
Counter=1;
Sequence(Counter)=i;
last_link=keepdata(2,i);
do j=i to dim(indata);
if keepdata(1,j) eq last_link then do;
Group_Circular_Path=catx('-',Group_Circular_Path,
keepdata(2,j));
Counter+1;
Sequence(Counter)=j;
last_link=keepdata(2,j);
*Found group circular path;
if keepdata(1,i) eq keepdata(2,j) then do;
do k=1 to Counter;
Normal_Path=
catx('-',keepdata(1,Sequence(k)),keepdata(2,Sequence(k)));
price=keepdata(3,Sequence(k));
quantity=keepdata(4,Sequence(k));
date=keepdata(5,Sequence(k));
time=keepdata(6,Sequence(k));
datetime=keepdata(7,Sequence(k));
output;
end;
leave;
end;
end;
end;
end;
end;
run;
--------
On Tue, 8 Sep 2009 15:44:43 +0530, Ashok R <r.ashokiyer@GMAIL.COM> wrote:
>Hi Arthur
>
>The output from your logic gives only the final output. like a-b,b-c,c-a.
>
>I would like to trace the transaction from a-b b-c and c-a. that would not
>be possible without having a unique key. If i take all the transactions
from
>a-b i would land up in getting wrong transactions. I would like to know the
>individual group transactions like
>
>a-b ,b-c and c-a. Please let me know how this can be made possible.
>
>Thanks
>Ashok
>
>On Mon, Sep 7, 2009 at 6:49 AM, Arthur Tabachneck
<art297@netscape.net>wrote:
>
>> Ashok,
>>
>> If I correctly understand what you are trying to do, then the
>> following either does it or comes close. I'm sure there is an even
>> better way using a hash, but this should at least get you started:
>>
>> Art
>>
>> data have;
>> infile datalines;
>> input symbol seller_code $ buyer_code $ date ;
>> informat date ddmmyy10.;
>> format date date9.;
>> datalines;
>> 100 20 22 15/10/08
>> 100 09 10 15/10/08
>> 100 22 21 15/10/08
>> 100 22 23 15/10/08
>> 100 23 29 15/10/08
>> 100 21 20 15/10/08
>> 100 01 02 15/10/08
>> 100 03 04 15/10/08
>> 100 02 06 15/10/08
>> 100 06 08 15/10/08
>> 100 08 02 15/10/08
>> 200 20 09 15/10/08
>> 200 09 10 15/10/08
>> 200 10 22 15/10/08
>> 200 22 23 15/10/08
>> 200 23 29 15/10/08
>> 200 21 20 15/10/08
>> 200 01 02 15/10/08
>> 200 03 04 15/10/08
>> 200 02 06 15/10/08
>> 200 04 08 15/10/08
>> 200 06 02 15/10/08
>> ;
>>
>> proc sort data=have;
>> by symbol;
>> run;
>>
>> proc transpose data=have out=transposed;
>> var seller_code buyer_code;
>> by symbol;
>> run;
>>
>> data want (keep=symbol track);
>> set transposed;
>> array indata(*) $ col:;
>> array sdata(32000) $;
>> retain sdata;
>> array bdata(32000) $;
>> format track $200.;
>> if mod(_n_,2) then do i=1 to dim(indata);
>> sdata(i)=indata(i);
>> end;
>> else do i=1 to dim(indata);
>> track=catx('->',sdata(i),indata(i));
>> last_link=indata(i);
>> do j=i to dim(indata);
>> if sdata(j) eq last_link then do;
>> track=catx('->',track,indata(j));
>> last_link=indata(j);
>> if sdata(i) eq indata(j) then do;
>> output;
>> j=dim(indata);
>> end;
>> end;
>> end;
>> end;
>> run;
>> --------------
>> On Sep 6, 9:52 am, Ashok <r.ashoki...@gmail.com> wrote:
>> > Hi All
>> >
>> > Hi
>> >
>> > I want a help in one of the logic. Iam trying to find out circular
>> > trade in share market where
>> > a sells to b and keeps on moving and again comes back to a.
>> >
>> > a-b b-c c-a.
>> >
>> > The possibilities are that b might also start a circular trade which
>> > needs to be captured. Please help me in this logic. Its very urgent.
>> > Please find the code and the sample data below.
>> >
>> > data test1;
>> > infile datalines;
>> > input symbol seller_code $ buyer_code $ date ;
>> > informat date ddmmyy10.;
>> > datalines;
>> > 100 20 22 15/10/08
>> > 100 09 10 15/10/08
>> > 100 22 21 15/10/08
>> > 100 22 23 15/10/08
>> > 100 23 29 15/10/08
>> > 100 21 20 15/10/08
>> > 100 01 02 15/10/08
>> > 100 03 04 15/10/08
>> > 100 02 06 15/10/08
>> > 100 06 08 15/10/08
>> > 100 06 02 15/10/08
>> > ;
>> > run;
>> >
>> > data test1;
>> > set test1;
>> > seller_code_pos=_n_;
>> > buyer_code_pos=_n_;
>> > run;
>> >
>> > data sellers(keep=symbol seller_code seller_code_pos);
>> > set test1;
>> > run;
>> >
>> > data buyers(drop=seller_code seller_code_pos);
>> > set test1;
>> > run;
>> >
>> > proc sql;
>> > create table temp as select a.symbol ,a.seller_code,
>> > a.seller_code_pos,b.buyer_code,b.date,b.buyer_code_pos
>> > from sellers a ,buyers b where a.symbol=b.symbol and
>> > a.seller_code=b.buyer_code and b.buyer_code_pos > a.seller_code_pos;
>> > quit;
>> >
>> > data temp (keep=symbol Suspect);
>> > set temp;
>> > Suspect= 'yes';
>> > run;
>> > proc sort data=test1;
>> > by symbol;
>> > run;
>> >
>> > proc sort data=temp;
>> > by symbol;
>> > run;
>> > data prob_suspect;
>> > merge test1 (in=a) temp(in=b);
>> > by symbol;
>> > if a and b then output;
>> > run;
>> >
>> > /* Remove The Unwanted Links */
>> > proc sort data= prob_suspect;
>> > by symbol seller_code;
>> > run;
>> > data Seller_temp (keep= symbol seller_code );
>> > set prob_suspect;
>> > by symbol seller_code;
>> > if first.seller_code then output;
>> > run;
>> > proc sort data= prob_suspect;
>> > by symbol buyer_code;
>> > run;
>> > data buyer_temp (keep= symbol buyer_code );
>> > set prob_suspect;
>> > by symbol buyer_code;
>> > if first.buyer_code then output;
>> > run;
>> >
>> > proc sql;
>> > create table reqd_data as select a.symbol, a.seller_code, b.buyer_code
>> > from seller_temp a, buyer_temp b where
>> > a.symbol = b.symbol and a.seller_code=b.buyer_code;
>> > quit;
>> > proc sql;
>> > create table fin_merge as select a.symbol,a.buyer_code, a.date,
>> > b.seller_code from prob_suspect a,reqd_data b
>> > where a.symbol=b.symbol and a.seller_code=b.seller_code;
>> > quit;
>> > proc sort data=fin_merge;
>> > by symbol seller_code;
>> > run;
>> > proc sort data=fin_merge;
>> > by symbol seller_code buyer_code;
>> > run;
>> >
>> > data prob_suspect_final (drop=loop_break link_test loop_start
>> > Lopp_Start_num Loop_Break Loop_Break_Num len1 complete_loop
>> > );
>> > set fin_merge;
>> > length path $ 25500.;
>> > length old_path $ 20000;
>> > length check_path $ 20000;
>> > by symbol seller_code buyer_code;
>> > link_test=lag(buyer_code);
>> > if upcase(link_test) = upcase(seller_code) then do;
>> > Loop_Start='Y';
>> > Lopp_Start_num=_n_;
>> > end;
>> > if upcase(link_test) NE upcase(seller_code) then do;
>> > Loop_Break= 'Y' ;
>> > Loop_Break_Num= _n_ - 1;
>> > end;
>> > retain path;
>> > retain old_path;
>> > retain check_path;
>> > if first.symbol then do;
>> > old_path=compress(seller_code||'-'|| buyer_code);
>> > path=compress(seller_code||'-'|| buyer_code);
>> > check_path=compress(seller_code||'-'|| buyer_code);
>> > end;
>> > if not first.symbol then do;
>> > check_path=compress(check_path ||'-'|| buyer_code);
>> > if loop_break='Y' then do;
>> > path=compress(path|| '-' ||buyer_code);
>> > end;
>> > old_path=compress(old_path ||',' || seller_code || '-' || buyer_code);
>> > end;
>> > if loop_break='Y' or first.symbol then do;
>> > path = compress(seller_code||'-'|| buyer_code);
>> > end;
>> > if not first.symbol then do;
>> > if loop_break='' then do;
>> > path=compress(path||'-'||buyer_code);
>> > end;
>> > end;
>> > len1=length(path);
>> > if strip(scan(path,1)) = strip(scan(path,-1)) then do;
>> > complete_loop='Y';
>> > end;
>> > run;
>> > proc sql;
>> > create table prob_suspect2 as select a.symbol,
>> > a.seller_code,a.path,a.old_path,a.check_path, a.date, b.buyer_code
>> > from prob_suspect_final a,
>> > reqd_data b where a.symbol=b.symbol and a.seller_code =b.seller_code;
>> > quit;
>> > /*
>> > proc sort data= prob_suspect2;
>> > by symbol seller_code path;
>> > run;
>> > proc sort data=prob_suspect2;
>> > by symbol old__path;
>> > run;
>> > */
>> >
>> > data check_code(keep= symbol check_code) prob_suspect_final1 (keep=
>> > reqd_buyer symbol date seller_code buyer_code path old_path
>> > check_path);
>> > set prob_suspect2;
>> > by symbol ;
>> > Reqd_Val=strip(scan(check_path,-1));
>> > Reqd_val_num=findc(check_path,reqd_val);
>> > if reqd_val_num=length(check_path) then delete;
>> > *if reqd_val ne strip(scan(path,-1))then delete;
>> > reqd_buyer=scan(check_path, -1);
>> > check_code=scan(path,-1);
>> > *if check_code not in (seller_code) then delete;
>> > run;
>> > data prob_suspect_final1;
>> > set prob_suspect_final1;
>> > reqd_path=tranwrd(old_path,',',"',");
>> > reqd_path=tranwrd(old_path,',',",'");
>> > run;
>>
>
>
>
>--
>Ashok
|
