Date:         Tue, 19 Jul 2005 09:35:05 -0400
Reply-To:     Jim Groeneveld <jim1stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim1stat@YAHOO.CO.UK>
Subject:      Re: can SQL do many-to-many merge
Comments: To: Kiran Kumar <kk.chikky@GMAIL.COM>

Hi Kiran,

Your data step can do it well after some adaptations:

options ps=50 ls=80;

data trades;
input date date9. timestamp price Quantity;
format date date9.;
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;

PROC SORT DATA=Trades NODUPS; BY Date TimeStamp; RUN;
PROC SORT DATA=Test NODUPS; BY Date TimeStamp; RUN;

data consolidate;
merge trades(in=a) test(in=b);
BY Date TimeStamp;
* if a and b then output; * has no influence;
keep date timestamp price Quantity PreBestBuy PreBestSell PostBestBuy
postBestSell;
run;

proc print data=consolidate; run;

Regards - Jim.
--
Y. (Jim) Groeneveld, MSc., Biostatistician, Vitatron b.v., NL
Jim.Groeneveld_AT_Vitatron.com (replace _AT_ by AT sign)
http://www.vitatron.com, http://home.hccnet.nl/jim.groeneveld

My computer always teaches me something new I thought I knew already.

[common disclaimer]

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