topic Re: Match with Multiple Occurence of ID in SAS Programming

Match with Multiple Occurence of ID

q1234 — Tue, 19 Apr 2011 13:59:08 GMT

Hi,

I want to match two datasets.

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

Where the out pushould look 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

Re: Match with Multiple Occurence of ID

art297 — Tue, 19 Apr 2011 15:22:16 GMT

I have always found proc sql the best and easiest way of accomplish many-to-many match merges.

However, I don't understand your desired output, either in terms of what is included or how it should be sorted.

Does the following do what you are trying to accomplish?:

proc sql noprint;
create table want as
select a.ID2, b.*
from datasets1 a, dataset2 b
where a.id=b.id
order by input(substr(ID2,2),best12.) desc, date
;
quit;

Art
-----------
> Hi,
>
> I want to match two datasets.
>
> 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
>
>
>
> Where the out pushould look 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

Re: Match with Multiple Occurence of ID

q1234 — Tue, 19 Apr 2011 16:28:55 GMT

Hi,

I have changed the order of the datasets in my previous code and it works perfectly.

Many thanks for your help