I have following two datasets:
First dataset: all
Date Sedol Price
13/03/2000 4010470 118
14/03/2000 4010470 117
15/03/2000 4010470 116
16/03/2000 4010470 115
13/03/2000 4012625 18
14/03/2000 4012625 17
15/03/2000 4012625 16
16/03/2000 4012625 15
13/03/2000 4383934 8
14/03/2000 4383934 7
15/03/2000 4383934 6
16/03/2000 4383934 5
Second Dataset: event_date
Sedol event date
4010470 14/03/2000
4010470 04/01/2002
4012625 18/12/2008
4383934 02/02/1996
4383934 10/07/2013
I would like to create a dataset like this
Date Sedol Price Event Date
13/03/2000 4010470 118 14/03/2000
14/03/2000 4010470 117 14/03/2000
15/03/2000 4010470 116 14/03/2000
16/03/2000 4010470 115 14/03/2000
13/03/2000 4010470 118 04/01/2002
14/03/2000 4010470 117 04/01/2002
15/03/2000 4010470 116 04/01/2002
16/03/2000 4010470 115 04/01/2002
13/03/2000 4012625 18 18/12/2008
14/03/2000 4012625 17 18/12/2008
15/03/2000 4012625 16 18/12/2008
16/03/2000 4012625 15 18/12/2008
13/03/2000 4383934 8 02/02/1996
14/03/2000 4383934 7 02/02/1996
15/03/2000 4383934 6 02/02/1996
16/03/2000 4383934 5 02/02/1996
16/03/2000 4012625 15 18/12/2008
13/03/2000 4383934 8 10/07/2013
14/03/2000 4383934 7 10/07/2013
15/03/2000 4383934 6 10/07/2013
16/03/2000 4383934 5 10/07/2013
Can someone please help? Thanks in advance.
data have1;
input Date:ddmmyy10. Sedol Price;
format date date9.;
cards;;
13/03/2000 4010470 118
14/03/2000 4010470 117
15/03/2000 4010470 116
16/03/2000 4010470 115
13/03/2000 4012625 18
14/03/2000 4012625 17
15/03/2000 4012625 16
16/03/2000 4012625 15
13/03/2000 4383934 8
14/03/2000 4383934 7
15/03/2000 4383934 6
16/03/2000 4383934 5
;
;
data have2;
input Sedol event_date:ddmmyy10.;
format event_date date9.;
cards;
4010470 14/03/2000
4010470 04/01/2002
4012625 18/12/2008
4383934 02/02/1996
4383934 10/07/2013
;
proc sql;
create table test as select a.*,b.event_date from have1 as a , have2 as b where a.sedol=b.sedol order by a.sedol,b.event_date;
quit;
Sure.
proc sql;
create table want as
select *
from
all inner join
event_date on all.sedol=event_date.sedol;
quit;
data have1;
input Date:ddmmyy10. Sedol Price;
format date date9.;
cards;;
13/03/2000 4010470 118
14/03/2000 4010470 117
15/03/2000 4010470 116
16/03/2000 4010470 115
13/03/2000 4012625 18
14/03/2000 4012625 17
15/03/2000 4012625 16
16/03/2000 4012625 15
13/03/2000 4383934 8
14/03/2000 4383934 7
15/03/2000 4383934 6
16/03/2000 4383934 5
;
;
data have2;
input Sedol event_date:ddmmyy10.;
format event_date date9.;
cards;
4010470 14/03/2000
4010470 04/01/2002
4012625 18/12/2008
4383934 02/02/1996
4383934 10/07/2013
;
proc sql;
create table test as select a.*,b.event_date from have1 as a , have2 as b where a.sedol=b.sedol order by a.sedol,b.event_date;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.