Need help with sql

[Ulaso]

Hello,
I'm a new user of SAS. I am trying to merge two files. However the identifier variable, in my case the order numbers, appear in both files in a different manner(It is market data, bid and asks appear in one file, and the actaul transactions in another, I am trying to match these two files).I was not able to merge them using MERGE function. I was told that SQL QUERY would let me merge these two files.
If anyone would kindly help me, I could send a part of the data.
Thanks in advance,
Ulaso.

 

[mdieckman]

Could you post a sample of the data in both tables? Without that - it's hard to see how you are trying to join. Here's a generic example of an equal join:

Note: "Extract" is my defined library.

PROC SQL;
     CREATE TABLE EXTRACT.FINAL   AS
     (SELECT
  	 A.*
	,B.*


     FROM EXTRACT.FILE1       	as A
         ,EXTRACT.FILE2  	as B

	 Where

A.COLUMN1 = B.COLUMN2
AND A.COLUMN2 = B.COLUMN2

     );
QUIT;

A left outer join (keep all records in left table and only those that match in right table) can be accomplised by changing to:

     FROM EXTRACT.FILE1       	as A
      left outer join EXTRACT.FILE2  	as B

	on

A.COLUMN1 = B.COLUMN2
AND A.COLUMN2 = B.COLUMN2

 

[Ulaso]

Hello,
Thanks for the respond. I'm attaching a sample of both files. The "Order" includes all the incoming orders for the security. The "Transaction" file includes the actual transactions. The identifier in the order file is order_no (it icludes all buyers and sellers), in the transaction file, buyers and sellers are matched with the price of transaction. What I'd like is to have all the prices (bid-ask-transaction) in one merged file using the order_no as the identifier.
In CSV file, I could not save the numbers properly, each time I use I have to go to cell-numeric and delete the decimals.
Thank you.
Order file:
Date Order No Security Pkod Order Type Quantity Price
4-Jan-07 401200700130259 ABANA E A 118 0.85
4-Jan-07 401200700129119 ABANA E S 214 0.85
4-Jan-07 401200700129055 ABANA E S 500 0.85
4-Jan-07 401200700128942 ABANA E S 500 0.86
4-Jan-07 401200700123463 ABANA E S 200 0.85
4-Jan-07 401200700121774 ABANA E S 200 0.86
4-Jan-07 401200700113318 ABANA E A 1000 0.81
4-Jan-07 401200700113302 ABANA E A 1000 0.82


Transaction file:
Date Security T_type Time Price Quantity Buyer_Order_No Seller_Order_No
4-Jan-07 ABANA B 14:01:05 0.88 3 401201000000000 401201000000000
4-Jan-07 ABANA B 14:02:45 0.87 10 401201000000000 401201000000000
4-Jan-07 ABANA B 14:03:06 0.86 1 401201000000000 401201000000000
4-Jan-07 ABANA B 14:15:58 0.86 2 401201000000000 401201000000000
4-Jan-07 ABANA B 14:18:36 0.85 36 401201000000000 401201000000000
4-Jan-07 ABANA B 14:18:36 0.85 36 401201000000000 401201000000000
4-Jan-07 ABANA B 14:19:36 0.85 128 401201000000000 401201000000000
4-Jan-07 ABANA B 14:27:51 0.85 500 401201000000000 401201000000000
4-Jan-07 ABANA B 14:28:57 0.85 136 401201000000000 401201000000000

 

[mdieckman]

Here is one way:


This will basically keep all values from transaction and pull in the prices from the order file without dropping anything. I'm not sure if I have interpreted exactly what you need, but hopefully this example will get you what you need or at least get you started. This can easily be flipped around where you keep the order information and pull in transaction info. You can also add addition criteria like t_type = 'B' (stuff of that nature).

PROC SQL;
     CREATE TABLE EXTRACT.FINAL   AS
     (SELECT

  A.Date    
, A.Security    
, A.T_type    
, A.Time    
, A.Price    
, A.Quantity    
, A.Buyer_Order_No
, B.Quantity AS BUYER_QTY   
, B.Price AS BUYER_PRICE
, B.Date    AS BUYER_DATE
, B.Security AS BUYER_SECURITY    
, B.Pkod    AS BUYER_PKOD
, B.Order_Type AS BUYER_ORDER_TYPE
, A.Seller_Order_No
, C.Quantity AS SELLER_QTY   
, C.Price AS SELLER_PRICE
, C.Date    AS SELLER_DATE
, C.Security AS SELLER_SECURITY    
, C.Pkod    AS SELLER_PKOD
, C.Order_Type AS SELLER_ORDER_TYPE


     FROM EXTRACT.Transaction         as A
     
	LEFT OUTER JOIN EXTRACT.Order    as B

     ON 

A.Buyer_Order_No = B.Order_No

	LEFT OUTER JOIN EXTRACT.Order    as B

     ON 

A.Seller_Order_No = B.Order_No



     );
QUIT;

 

[mdieckman]

Sorry - the alias on the join for seller should say as C not B again.

This part needs corrected:

    LEFT OUTER JOIN EXTRACT.Order    as C

     ON

A.Seller_Order_No = C.Order_No

 

[Ulaso]

Hi, codes you submitted worked as a format.Thank you for that. However, although I have 2252 rows in order file and 1011 rows in the transaction, in the merged file I ended up with more than 33 million rows.
I will try to figure out the reason.Thanks again for your help.

 

[Ulaso]

I fixed the problem, it was because of different formats I used in order numbers. After aligning that, I have a clean 1813 row table. Thanks again for your help.

 

[Ulaso]

Hello again,
I got the merged file. The problem now is there are repeating rows in the merged file; the order file has 2252 rows, and transaction file has 1011 rows, however merged file has over 1800 rows. Do you have any suggestions to fix this?
Thank you,