topic Re: How to perform a complex merge/matching procedure from two datasets (Part B) in SAS Procedures

How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Tue, 12 Feb 2013 17:39:20 GMT

Dear all,

I am posting two new questions which are based on an earlier inquiry which was partly answered regarding a complex merging procedure:

https://communities.sas.com/thread/39361?start=0&tstart=0

The two new questions are based on the provided solution by Ksharp:

1. The basic solution shown also below matches the same currencies based on a specific date and time interval. If there exists no exact match by time, the next closest time is selected to do the matching (thus time+1 as in code). What I would like is to update this code to perform the same procedure as before instead but now instead of having time A = time B (or time B+1) to have time A = time B+60 (or time B+60 + 1).

2. The solution is provided under hash format and when executing one currency for one day it works well. However my dataset has multiple currencies and 68 days of observations (in total 64 million of observations in the first dataset and 2mln in the second dataset) which makes the execution impossible by my pc. Do you have any other ideas on how efficiently to execute the hash code?

Any suggestions are very much welcome.

Many thanks for your time in advance

Best regards

Neo

Below the hash solution

libname'd:\phd thesis\sas files';

data neo.c2;

set neo.Chfdaily2;

datetime=input(catx(':',value_date,newtime),anydtdtm20.);

format datetime datetime.;

run;

data neo.c3;

set neo.Chfr;

datetime=dhms(datepart(Date_G_),0,0,int(Time_G_));

format datetime datetime.;

run;

data neo.want;

if _n_ eq 1 then do;

if 0 then set c3;

declare hash ha(hashexp:16,dataset:'c3');

ha.definekey('_ric2','datetime');

ha.definedata(all:'Y');

ha.definedone();

end;

call missing(of _all_);

set c2;

_ric2 =cur1cur2;

datepart=datepart(datetime);

do while(ha.find() ne 0);

datetime+1;

if datepart ne datepart(datetime) then leave;

end;

drop datepart LOC Price Volume;

run;

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Tue, 12 Feb 2013 22:38:32 GMT

It will help others to try to answer if you can express what you want to accomplish a bit differently. I.e., without referring to KSharp's code, or references like 60+1, what are you really trying to accomplish?

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Thu, 14 Feb 2013 07:40:09 GMT

Hi,

So we deal with the following economic problem:

- we have two datasets. The first one contains the transactions of a broker with customers. the second dataset contains concluded transactions on the market (where only brokers have access but not customers).

- what i try to do is to compare the difference in the price the customer has paid to trade with the broker and the price which at that time was prevailing at the market.

Is it more clear now?

That is why we need to match from the two datasets the currencies if they are the same (since both datasets have transactions of several currencies), then look at the dates and match the same dates and as last step match the transaction times - they should also be the same. Now comes the tricky part:

1 Because the broker after transacting with the customer might not trade exactly the same time with other brokers but after 1 second, the first step of the solution as you see in the hash solution checks if time A does not euqal time B, it picks time B+1. This is the first part of the problem

2 Now imagine I want to directly match the transaction a broker executed with a customer with the price which was on the market (dataset B) 60 seconds after the customer transaction. However as before, there might exist no price in exactly +60 seconds, so the code must account for that and searchh for 60seconds + X, that is take exactly the nearest observation.

No2 of the problem as described above does not work in the hash solution

For No1 and potentially no2 the hash solution is not efficient as a method to execute a large dataset

Let me know if you need any additional info and look forward to your reply. Attached are also excel sheets with sample data

Thanks

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Fri, 15 Feb 2013 00:17:53 GMT

No, not clear enough for me. If dataset A has the values that one paid at a particular point in time, and dataset B contains the prices at various points in time, I would think that for each record in dataset A you would want the record from dataset B that has a time that occured closest to but before the time reflected in the particular dataset A record.

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Fri, 15 Feb 2013 20:41:26 GMT

Let me me explain further:

Imagine there exist two parallel markets, market A (dataset A) where customers are allowed to trade with brokers(e.g banks) and market B (Dataset B) where only brokers are allowed to trade between themselves.

Now lets say we would like to test whether some customers are better informed than their broker. We check how much they paid when they transacted with the broker and then we check the price the broker paid when trading with other brokers, exactly the same time or later during the day depending on the investment horizon. A numeric example.

Customer A (is informed)

Buys from our broker (dataset A) an asset XYZ at $5 at 10.00am on 07.02.2013

Our broker sells to other brokers (dataset B) the same asset as $6 at 10.01 on 07.02.2013

Our broker also sells to other brokers (dataset B) the same asset at $7 at 11.30 on the 07.02.2013

What is the price impact of the customer (simply put the delta between the first trade with the broker and any of the other trades between the brokers?)

From the above we can analyze first of all whether the customer is more informed about future price changes than his broker(he knows the direction of price change, e.g buys before the price goes high) and of course up to what time horizon he has information. Is it intraday for a couple of minutes? hours? or it is days?

For this reason I need to program the matching code at different time horizons. This variable (price impact) will be my dependent variable

I hope this makes things clearer?

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Fri, 15 Feb 2013 22:22:57 GMT

Not for me! You don't say what you think will constitute the "other trades" and you seem to be talking about specific brokers but, at the same time, generalizing to the market place. I would think, again, that you want the most recent broker price for the asset before the datasetA record, as well as all of those within a certain period after the trade.

If that is what you are trying to do, then you have to let us know and what that after period should be.

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sat, 16 Feb 2013 12:09:42 GMT

Actually I would like to take the broker price executed at the same time as the record in dataset A.if not possible then use the broker price in time+1(the next closest). The other broker trades will have to be after the trade record in dataset A in specific time intervals. for example executed time in dataset A + 30 seconds or 60 or 90 etc. I would imagine the solution code as a standard solution then me being able to modify the periods of analysis(30 or 60 or 90 secods). this solution should deliver values in specific points in time.

Let me restate the problem in a similar way

1. I would like to match based on a trade for example EUR/USD (asset) from dataset A executed at a specific point in time (date and time), with a trade of EUR/USD (same asset as in dataset A) which is part of dataset B and is executed at the same point of time (date and time) with the trade in dataset A. If such trade to be matched at exactly the same point of time does not exist, to simply search and then match the closest trade available (e.g time +1). This is what the hash solution actually did.

That is, is there a more efficient code to perform the above?

2. The second exercise is to do exactly the same process as above, however this time I would like to match the trade in dataset B which is X seconds after the trade in dataset A. that means that we will match trade A at a specified time and date (lets say 10:00 16/02/2013 ) with a trade in dataset B which is the same date, but time+60 seconds (lets say 10:01 16/02/2013). if there does not exist a perfect match at time+60seconds to take the closest one, e.g time+60seconds+1(e.g 10:01:01 16/02/2013)

In the files attached there is also available a sheet which shows how the final matching should look like

Let me know how I can support further in explaining this exercise

Thanks

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sat, 16 Feb 2013 14:44:26 GMT

Neo,

Your 'want' file doesn't appear to match your description. From your description, I would have expected to find at least one record in your want file for every record that exists in dataset A. However, the first one there IIRC, was record number six. Why?

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sat, 16 Feb 2013 17:52:12 GMT

Hi,

The want file is structured as follows: up to variable datetime (on the horizontal axis) these are dataset B variables and then follow dataset A variables. Datasets A and B follow the notation of our discussion from above

As you correctly observed the correctly matched observations start from observation no 6. The first 5 observations which are not matched I believe this is the case because the example files of dataset B which I provided was too short, thus did not had enough observations (i wanted to upload only a small part of the whole dataset). However the complete dataset B will not face such problems. For illustration, dataset A has 2 millions observations for the same time period which dataset B has 64 million observations.

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sat, 16 Feb 2013 18:29:46 GMT

My question probably wasn't sufficiently clear. What is the criterion for a record not matching? I ask because I would suggest code that gets all of the relevant data in one step. Within a minute? An hour? And, as for multiple matches, does that change the criterion or do you want all matches within a particular time period?

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sat, 16 Feb 2013 20:39:03 GMT

Hi Art,

records need to fullfill the following criteria to be ,matched :

1. same currency(asset)

2. same date

3. same time

in general records sometimes do not match because time is measured up to the second (this is too detailed) and sometimes there exist no exact matches by the second in both datasets(this is why i would like the code to search for the next closest observation in terms of seconds.

in the example discussed above with the "want" file, the problem was that in dataset B there were no trades after 21:00 so for the trade executed in dataset A at 21:50 there was no matching trade in dataset B. this was because I added a short example (small sample) of the two datasets.

the above will net only one new variable (matched exactly by time or in the worst case matched by time+1)

the second matching exercise will search for a solution time in A = time in B+ 30 seconds, +60 seconds, 30min, 60mins, 720mins,24hrs (this should add 6 variables). I can though adjust the code from above and execute it 6 times to give each time one solution) . This solution will be more tricky so lets discuss in due time.

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sat, 16 Feb 2013 21:05:33 GMT

Neo,

You're getting closer but, like I said, I would suggest getting everything done in one run. Does time+1 equal time+1 second?

If so, then what I would suggest is outputting the info for all six matches (i.e., the average price for those records which match:

(1) time <=time+1second

(2) time+1 second <= time+30 seconds

(3) time + 30 seconds <= time+30 minutes

(4) time+30 minutes <= time+60 minutes

(5) time+60 minutes <= to,e+720 minutes

(6) time+720 minutes <= time+24 hours

Would that suffice?

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sat, 16 Feb 2013 21:52:59 GMT

I refer to Time+1 as the closest observation next to time. That is, if time in dataset A does not equal time in B then +1 in my wording meant simply the next closest observation, this can be 1 second or 10 seconds or 2mins,dependng on dataset B.Some currencies (assets) are more liquid than others, that means the next closest observation might be after one second, but for currencies of small countries the next closest trade might be in 2 mins as these currencies are not trade so often.

the base for all the matches will be time in dataset A, I modified slightly what you wrote:

(1) time(in dataset A) <=time(in dataset B)+1second

however for the next observations things change:

(2) time <= time+30 seconds

(3) time <= time+1 minutes

(4) time <= time+30 minutes

(5) time <= time+60 minutes

(6) time <= time+24 hours

as you can see mathematically i have the price from dataset A and the development of the price in time based on data available in dataset B.

at the end i will get a row e.g

(dataset A) (dataset B)

trade executed price time price2 time2 price3 time3 ........... price6 time 6

1 1.35 22:36:00 1.37 22:36:00 1.38 22:36:30 1.36 22:36:00 (however this is after 24 hrs - the date has changed)

2 ............................

3 ..........................

.....

2mln ............................

I think it is more difficult to do all matches as once. This is shown in my example. Have a look at price 6/time 6. As this query is conditional:

a/ on the currency variable (that both currency codes are the same in dataset A and B)

b/ on the date

However if we search for a trade + 24hrs or in the above example +2 hours based on the trade which is executed at 22:36 then the dates will be not the same. I am not good in programming but I think this will be tricky to solve as the basic solution is conditional on that the dates are the same. if we do not condition on date in the beginning, the solution will match based on time and net wrong results because the dataset is a time series of 3 months (total of 68 working days) thus practically we will have an exact solution at 22:36 for 68 observations contained in dataset B.

Hope I did not confuse you with the above

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sat, 16 Feb 2013 23:24:11 GMT

Neo,

Since you have both date and time in one file, and a datetime field in the other, I would just combine the two in the first file to have a datetime field in both. That way, you only have to compare datetime differences and whether they cross dates would be irrelevant.

If I can find the time to offer some code, it will be to solve everything at once. How you use it, of course, would be your decision.

Your requirement for the initial "match" should change, I think, the rest of the ranges. I.e., if first match is the closest time, then I would think that you would want the ranges to appear something like:

(1) datetime(in dataset A) <= the closest datetime in dataset B)

new_base_datetime = the time found in this step

(2) new_base_datetime+1/100th second <= next datetime(s) in datasetB<=new_base_datetime+30 seconds

(3) new_base_datetime+30 seconds+1/100th second <= next datetime(s) in datasetB<=new_base_datetime+1 minute

(4) new_base_datetime+1 minute+1/100th second <= next datetime(s) in datasetB<=new_base_datetime+30 minutes

(5) new_base_datetime+30 minutes+1/100th second <= next datetime(s) in datasetB<=new_base_datetime+60 minutes

(6) new_base_datetime+60 minutes+1/100th second <= next datetime(s) in datasetB<=new_base_datetime+24 hours

Would that work or do I still not understand what you are trying to do?

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sun, 17 Feb 2013 00:00:27 GMT

Hi Art,

What you mention rgd datetime makes sense then this should not be a problem.

The solution will be actually easier than what you propose. The base time is fixed. That means for all the matches To (starting time) is constant and it is the datetime of a transaction in dataset A. we then simply match this one with those in dataset B which are +30seconds, +1...+24hrs further in time. as you have written it above equations (1) to (6), the left handside should be always datetime (in dataset A)

in summary:

(1) datetime(in dataset A) <= the closest datetime in dataset B)

(2) datetime(in dataset A) <= database B_datetime+30 seconds

(3) datetime(in dataset A) <= database B_datetime+1 minute

(4) datetime(in dataset A) <= database B_datetime+30 minutes

(5) datetime(in dataset A) <= database B_datetime+60 minutes

(6) datetime(in dataset A) <= database B_datetime+24 hours

does it make sense?

all the above conditioned on currency in database A = currency in database B (this is a text variable, i am not sure if this makes a difference for the code)

i am not sure if we need this part you provided in all the equations:

<= next datetime(s) in datasetB ?

I have the feeling we are close to have the solution described in a logical order.

Thanks

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sun, 17 Feb 2013 04:14:54 GMT

I was suggesting making all of the times relative to the closest matching time based on your comment that "that means the next closest observation might be after one second, but for currencies of small countries the next closest trade might be in 2 mins as these currencies are not trade so often".

That is why I suggested your making the categories relative to the first match. However, I'm a Psychologist, so this is NOT really even close to my areas of expertise. And, besides, you (not me) are the decision maker.

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sun, 17 Feb 2013 12:45:13 GMT

Hi Art,

I think if we treat this as two sub problems will be easier to solve. The first problem is datetime (in dataset A) <= the closest datetime (in dataset B) and then the second problem will be matching datetime (in dataset A) <= database B_datetime "+30 seconds / 1min / 30mins / 60mins / 24hrs as discussed in earlier posts" which will give us 5 more variables in the output.

I would suggest if you have already a code in mind to post it here so that I test it, then I think will be more easier to finalize the solution with small iterations

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sun, 17 Feb 2013 18:00:05 GMT

Your call! In your original post, had mentioned that some code had offered to solve a similar problem might be just what you were looking for. I still don't understand what you want for the initial match, but here is a modified version of the code that PGStats had offered. It simply gets the first Reuters' record that is greater than or equal to the chfdaily record. It assumes that the Reuters' data in sorted in datetime order:

libname art "c:\art";

data chfdaily2 (drop=value_date newtime);

length Cur1Cur2 $7;

format Cur1Cur2 $7.;

informat Cur1Cur2 $7.;

set art.chfdaily2;

format dt1 datetime19.;

dt1=dhms(input(value_date,ddmmyy10.),

hour(input(newtime,time8.)),

minute(input(newtime,time8.)),

second(input(newtime,time8.)));

run;

proc sort data=chfdaily2;

by dt1;

run;

data chfreuters3 (drop=date_G_ time_G_);

set art.chfreuters3;

format dt2 datetime19.;

dt2=dhms(datepart(date_G_),hour(time_G_),minute(time_G_),second(time_G_));

run;

data want;

retain start_rpointer;

set chfdaily2;

if _n_ eq 1 then start_rpointer=1;

current_rpointer=start_rpointer;

do while (dt1 ge dt2 and not have2end);

set chfreuters3 point=current_rpointer end=have2end;

if dt2 ge dt1 and Cur1Cur2 eq _ric2 then do;

output;

start_rpointer=current_rpointer;

end;

else do;

current_rpointer+1;

end;

run;

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

machete — Sun, 24 Feb 2013 11:53:25 GMT

Dear Art,

Thanks for the reply. This code worked for doing the first match. However I am facing two problems:

1. When I try to run this on a larger sample - one currency (14000 observations in the first dataset to be matched from 1,5million from Reuters) for the whole period (68 days) the system crashes after two hours of processing.I tried this a couple of times with other combinations of currencies but it still crashes. Is there a way we can adjust the code to execute stepwise day by day(which will mean to break down the dataset into 68 days automatically and then re-merge it)? Breaking down the dataset manually into days and currencies will take me probably days to do.Do you have any other possible solutions?

2. Can you provide an update solving my second problem as well - matching the reuters records which are 30,60,720 seconds further in time than the record in Dataset A etc Or shall we find a stable solution for point no 1 from above first and then deal with this one?

Thanks

Neo

Re: How to perform a complex merge/matching procedure from two datasets (Part B)

art297 — Sun, 24 Feb 2013 14:18:08 GMT

Without knowing why it crashed, or actually seeing the data, it is impossible to say. Given that the code is rather straight forward, my guess would be that the Reuters data is not in datetime order or, possibly, it is in received datetime order, but has to be adjusted for timezones in order for the datetimes to be sequentially ordered. Have you manually looked at a section of the data (say, possibly, a one hour span in the middle of some weekday)?

From what you would observe, the solution may be obvious.