topic Re: Match many to many in SAS Procedures

Match many to many

q1234 — Tue, 19 Apr 2011 10:33:59 GMT

Hi,
Please any one can help me to do the following match?
I have two data sets 1 and 2 where I have have the same ID

dataset1
ID ID2
X C25
X C23
G C24
G C5
D C8
D C9

dataset2
ID price1 price 2 price3
X .5 .9 1.4
G .6 .7 .9
D .4 1.3 .8

the expected out put should like this

ID ID2 price1 price2 price3
X C25 .5 .9 1.4
X C23 .5 .9 1.4
G C24 .6 .7 .9
G C5 .6 .7 .9
D.............
My current code after sorting the datasets by ID is as follows

data new;
merge dataset1 dataset2;
by ID;
run;

The problem is that each ID from dataset2 match just one ID from dataset 1.
I need to modify this code in order to give the price information for the ID in datasets 1

Many thanks

Re: Match many to many

DF — Tue, 19 Apr 2011 11:24:06 GMT

Some sample data/code for anyone looking at this:

data dataset1;
format id $1.;
format id2 $3.;
infile datalines dsd delimiter=' ';
input id $ id2 $;
datalines;
X C25
X C23
G C24
G C5
D C8
D C9
;
run;

data dataset2;
format id $1.;
format price1 5.2;
format price2 5.2;
format price3 5.2;
infile datalines dsd delimiter=' ';
input id $ price1 price2 price3;
datalines;
X .5 .9 1.4
G .6 .7 .9
D .4 1.3 .8
;
run;

proc sort data=dataset1;
by id;
run;

proc sort data=dataset2;
by id;
run;

data dataset3;
merge dataset1 dataset2;
by id;
run;

Dataset 3 looks like:

D C8 0.40 1.30 0.80
D C9 0.40 1.30 0.80
G C24 0.60 0.70 0.90
G C5 0.60 0.70 0.90
X C25 0.50 0.90 1.40
X C23 0.50 0.90 1.40

In response to your question - I'm not sure what the problem is? The above code, taken from the description of what you've done, seems to do exactly what you need and the output matches your requirement.

The only condition would have to be that dataset2 must be unique by ID, which is the case at least for your sample data.

Re: Match many to many

AmarMundankar — Tue, 19 Apr 2011 11:48:36 GMT

Hi ,
You can try the following if you can use the PROC SQL for your solution.

data ds1;
input ID $ ID2 $;
cards;
X C25
X C23
G C24
G C5
D C8
D C9
;
data ds2;
input ID $ price1 price2 price3;
cards;
X .5 .9 1.4
G .6 .7 .9
D .4 1.3 .8
;
proc sql;
create table ds3 as
select * from ds1,ds2 where ds1.id = ds2.id;
quit;

Thanks,
Amar Mundankar.

Re: Match many to many

q1234 — Tue, 19 Apr 2011 11:55:08 GMT

Hi,

Many thanks for your replay. It seems that I have not explained the problem very well.
this is the current format of my 2 datasets for monthly stock price

datasets 1
ID ID2
X C25
X C23
G C24
G C5
D C8
D C9

data dataset
ID price date
X 0.40 01/01/2000
X 0.41 01/02/2000
X 0.42 01/03/2000
X 0.40 01/04/2000
X 0.44 01/05/2000
X 0.39 01/06/2000
X 0.20 01/01/2000
D 0.21 01/02/2000
D 0.20 01/03/2000
D 0.23 01/04/2000
D 0.24 01/05/2000
D 0.26 01/06/2000

I want the out put looks like this

ID2 ID price date
C25 X 0.40 01/01/2000
C25 X 0.41 01/02/2000
C25 X 0.42 01/03/2000
C25 X 0.40 01/04/2000
C25 X 0.44 01/05/2000
C25 X 0.39 01/06/2000
C23 X 0.40 01/01/2000
C23 X 0.41 01/02/2000
C23 X 0.42 01/03/2000
C23 X 0.40 01/04/2000
C23 X 0.44 01/05/2000
C23 X 0.39 01/06/2000
C9 D 0.21 01/01/2000
C8 D 0.21 01/02/2000
C8 D 0.20 01/03/2000
C8 D 0.23 01/04/2000
C8 D 0.24 01/05/2000
C8 D 0.26 01/06/2000
C9 D 0.21 01/01/2000
C9 D 0.21 01/02/2000
C9 D 0.20 01/03/2000
C9 D 0.23 01/04/2000
C9 D 0.24 01/05/2000
C9 D 0.26 01/06/2000

The main thing is to ensure the new datsets includes the ID2 with all the stock price.

In other words, it is match many-to-many, where I have multiple occurence for ID in both the datasets.

I am working on this code right now, but I could not egt what I want. So may you can help to modify this code

(datasets1=data1 dataset2=data2)

_________________________________________________________
data index;
keep from1 to2 ID;
retain from1;
set data1(keep= ID);
by ID;
if first.ID then from1=_N_;
if last.ID then do;
to2=_N_;
output;
end;
run;

data finalmatch;
merge data2 (IN=in_lhs)
index (IN=in_ndx);
by ID;
if in_lhs and in_ndx;
Do from_to=from1 to to2;
set data1 point=from_to;
output;
end;
run;
___________________________

Many thanks Message was edited by: q1234