Create table with repeated observations based on event date and company identifier

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Create table with repeated observations based on event date and company identifier

 

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.

 


Accepted Solutions
Solution
‎04-15-2018 03:15 AM
Trusted Advisor
Posts: 1,147

Re: Create table with repeated observations based on event date and company identifier

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;
Thanks,
Jag

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,541

Re: Create table with repeated observations based on event date and company identifier

Sure.

 

proc sql;
create table want as
select * 
from 
    all inner join
    event_date on all.sedol=event_date.sedol;
quit;
PG
Solution
‎04-15-2018 03:15 AM
Trusted Advisor
Posts: 1,147

Re: Create table with repeated observations based on event date and company identifier

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;
Thanks,
Jag
New Contributor
Posts: 2

Re: Create table with repeated observations based on event date and company identifier

Posted in reply to Jagadishkatam
Thanks Jag! It works perfectly!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 189 views
  • 0 likes
  • 3 in conversation