As Sig pointed out, your code has no BY statement. Thus SAS has no idea that you wish to match records by date and time.

Your example is weak in that all of your observations with same date and time values are also identical in terms of the satellite variables (PreBestBuy etc.). Is this always the case? Then just de-duplicate and you have a many-to-one match. Otherwise, please explain your business rule for deciding which TEST observation to use with each TRADE observation.

Both the DATA step and PROC SQL handle many-to-many matching, but they work much differently. I cannot recommend one over the other without knowing the specific requirements at hand.

On Tue, 19 Jul 2005 14:08:00 +0530, Kiran Kumar <kk.chikky@GMAIL.COM> wrote:

>Dear SAS users: > >Still trying to find a way out for merging two datasets, both having >multiple records on common variables. > >Specifically, I have two datasets, trade and test dataset. >data trades; >input date date9. timestamp price Quantity; >cards; >01JAN2004 600 50 2 >01JAN2004 660 53 10 >02JAN2004 760 54 5 >02JAN2004 760 53 3 >; >run; >data test; >input date date9. timestamp PreBestBuy PreBestSell PostBestBuy PostBestSell; >cards; >01JAN2004 600 48 52 50 53 >01JAN2004 660 49 54 53 55 >01JAN2004 660 49 54 53 55 >02JAN2004 760 54 55 56 57 >02JAN2004 760 54 55 56 57 >02JAN2004 760 54 55 56 57 >02JAN2004 760 54 55 56 57 >; >run; > >Would like to get "consolidate" dataset by merging the datasets on >common variables date and timestamp. Desired output "consolidate" >should have the following records: >data consolidate; >input date date9. timestamp price Quantity PreBestBuy PreBestSell >PostBestBuy PostBestSell; >cards; >01JAN2004 600 50 2 48 52 50 53 >01JAN2004 660 53 10 49 54 53 55 >02JAN2004 760 54 5 54 55 56 57 >02JAN2004 760 53 3 54 55 56 57 >; >run; > >To get the above "consolidate" dataset, I am using the following >datastep command: >data consolidate; >merge trades(in=a) test(in=b); >if a and b then output; >keep date timestamp price Quantity PreBestBuy PreBestSell PostBestBuy >postBestSell; >run; >which gives the following records. >01JAN2004 600 50 2 48 52 50 53 >01JAN2004 660 53 10 49 54 53 55 >01JAN2004 660 54 5 49 54 53 55 >02JAN2004 760 53 3 54 55 56 57 > > But the output donot match the requirement..as there is mismatching >of rows...for 01Jan2004 (3rd record) > >Some of my friends, suggested me to try with SQL many-to-many merge >instead of datastep. > >I am sure this task can be done easily in sas, request you to spare some time. > >Thank you, > >kk