question for determing the tranaction direction

[liufeng511]

Hi;
I have a question for determing the transaction direction. As the attachment dataset shows, the dataset have variables: time,trade price,ask price, bid price, midquote(=0,5*(bid+ask). the rules for determing the transaction are as follows:
1. Transactions which occur at prices higher(lower) than the midquotes are classified as being buyer-initiated(seller-initiated).

2. Transactions which occur at a price that equals the midquotes but is higher(lower) than the previous transaction price are classified being buyer-initiated(seller-initiated).

3.Transactions which occur at a price that equals both the midquote and previous transaction price but is higher(lower) than the last different transaction price are classified as being buyer-initiated(seller-initiated).

if determined as buyer-initiated;we signed it as "B", if judged as seller-initiated, signed it as "S".
The first step is easy to write sas code. At the second step, I can compare the current trade price with the previous transaction price using lag function. But the third step is difficult for me. How to find the last different transaction price? I think We cannot use lagn() function endlessly. So I resort help to us all! Thanks in advance!
The sample dataset is in the attachment.

[jrai]

There is only 1 obs which satisfied the criteria for the third condition & as you were facing problem with the third condition, I worked only on that. The approach is pretty similar to what I did in your last post i.e. using a hash table.

data ticker;
infile "c:\users\jatin rai\desktop\trade direction.txt" dlm='09'x;
input code $ time :datetime18. price bid ask midquote;
format time datetime18.;
if cmiss(of _all_)=0;
run;

data ticker;
n=_N_;
set ticker;
run;

data temp(drop=n price tp i rename=(cprice=price));
if _N_=1 then do;
	if 0 then set ticker;
	declare hash ticker(dataset:"ticker");
	ticker.definekey("n");
	ticker.definedata("price");
	ticker.definedone();
end;
set ticker(rename=(price=cprice));
if _N_>1 and cprice=midquote and cprice=lag1(cprice) then do;
	tp=cprice;
	do i=_N_ to 1 by -1 until(tp^=cprice);
		ticker.find(key:i);
		if cprice^=price then tp=price;
	end;
	if tp<cprice then label='B';
	else label='S';
end;
run;

Create a hash table with key= _N_ i.e. the number of obs. When the criteria for third condition is met, I created a variable called tp (short for transaction price) which stores the current price. Then program starts looking for prices in the hash table where obs number is less than the current obs number. For example, in our only case the observation number is 152. Therefore, the program will look at each value in the hash table starting from 152 backwards to 1 till it finds a value that is different from the current price. Once it finds a different value then it stores that value in tp and exits the loop. The label is decided based on value of tp compared with current price.

[liufeng511]

Dear Jrai:
Thank you! I can not expess my gratitude with my limited English words. I am a new beginner of SAS. I believe I will make progress at the special sas forum!
Liufeng511