Date:         Sun, 6 Sep 2009 14:07:46 -0700
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Circular Trading Logic Urgent
Content-Type: text/plain; charset=ISO-8859-1

The following was posted on the Google side of the list today.  I
would presume that a hash would provide the simplest solution but,
regardless and NOT URGENTLY, I too would like to see the possible
solutions.

Art
------------
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;