topic How to merge two data-set? in SAS Procedures

How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 11:28:51 GMT

I have two data

first one has say like below:

permno1 date

12345 20141111

23456 20141113

the second one has like below:

permno2 date

34567 20141111

45678 20141111

56789 20141111

34567 20141113

67890 20141113

I want to get:

permno1 permno2 date

12345 34567 20141111

12345 45678 20141111

12345 56789 20141111

23456 34567 20141113

23456 67890 20141113

How should I write the code?

Thank you

Re: How to merge two data-set?

LinusH — Fri, 12 Dec 2014 12:14:25 GMT

Just SQL join on date?

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 12:48:36 GMT

you mean?

I am a beginner

proc sql;

create table d as

select

a.*,

b.permno2

from d1 as a, d2 as b

where a.date=b.date;

???

Re: How to merge two data-set?

Kurt_Bremser — Fri, 12 Dec 2014 13:15:03 GMT

proc sql;

create table d as

select

a.permno1,

b.*

from d1 as a, d2 as b

where a.date=b.date;

quit;

gives you the correct order of columns.

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 13:43:02 GMT

so if I want to get a event window [-10,10]

suppose in d2, there are not only date in d1, but also other dates.

then

proc sql;

create table d as

select

a.permno1,

b.permno2,

b.date

from d1 as a, d2 as b

where a.date-10<=b.date<=a.date+10;

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 13:49:25 GMT

And what if I have dataset like below that d1 not change but d2 contain permno1, and d2 contain permno with different date from 1996 till now

first one has say like below:

permno1 date

12345 20141111

23456 20141113

the second one has like below:

permno2 date

12345 20141111

23456 20141111

34567 20141111

45678 20141111

56789 20141111

12345 20141113

23456 20141113

34567 20141113

67890 20141113

I want to get: where permno1<>permno2

permno1 permno2 date

12345 23456 20141111

12345 34567 20141111

12345 45678 20141111

12345 56789 20141111

23456 12345 20141113

23456 34567 20141113

23456 67890 20141113

How should I write the code?

proc sql;

create table d as

select

a.permno1,

b.permno2,

b.date

from d1 as a, d2 as b

where a.date-10<=b.date<=a.date+10 and a.permno1<>b.permno2;

then what if there are 1billion data?

it will take a long time to finish the sql.

Re: How to merge two data-set?

Kurt_Bremser — Fri, 12 Dec 2014 13:53:21 GMT

If you want to have only dates contained in d1, use the "left join".

Re: How to merge two data-set?

Kurt_Bremser — Fri, 12 Dec 2014 13:57:01 GMT

SQL can be sub-optimal when joining large datasets.

Sort the datasets with proc sort first (if they are not already sorted) and use a datastep with the merge and by statement.

Be aware that the data step merge cannot build a cartesian product when multiple by values are present in both datasets.

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 14:24:21 GMT

I don't only want data from d1

I only want the date from d1 and other data from d2.

So with a large dataset with say three variables need to match (sic permno eventdate date)

d1 has sic permno1 eventdate

d2 has sic permno2 date

I can use:

rename to make eventdate=date

then:

proc sort data=d1;

set d1;

by sic permno1 date;

proc sort data=d2;

set d2;

by sic permno2 date;

then I want sic are same, permno1<>permno2, and eventdate-10<=date<=eventdate+10

what should I write?

data merge;

merge d1 d1;

by sic date;?

or the order should be

by sic date permno1;

by sic date permno2?

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 14:28:39 GMT

date d1 (rename=(eventdate=date));

set d1;

proc sort data=d1;

set d1;

by sic date permno1;

proc sort data=d2;

set d2;

by sic date permno2;

data merge;

merge d1 d2;

by sic;

where

date>=date-10 and date<=date+10;

Re: How to merge two data-set?

Jiayi — Fri, 12 Dec 2014 14:35:54 GMT

Also

If I have two data

first one has say like below:

permno date

12345 20141111

23456 20141113

the second one has like below:

permno date

34567 20141111

45678 20141111

and I want

permno date

12345 20141111

23456 20141113

34567 20141111

45678 20141111

how could I do?

Re: How to merge two data-set?

venkatnaveen — Sun, 14 Dec 2014 14:17:13 GMT

proc sql;

select * from ds1

union

select * from ds2;

quit;

Re: How to merge two data-set?

swapnil — Wed, 17 Dec 2014 06:40:46 GMT

proc sort data=dataset1;

by date;

run;

proc sort data=dataset2;

by date;

run;

data dataset3;

merge dataset1(in=d1) dataset2(in=d2);

by date;

if d1 and d2;

run;