Merging datasets based on 2 variables in SAS.

Answer 1

Ok so it appears you can just do it very easily by:

proc sort data=work;
by ticker month;
run;
proc sort data=wsize;
by ticker month;
run;
data test;
merge work(in=a) wsize(in=b);
by ticker month;
frommerg=a;
fromwtvol=b;
run;
data test;
set test;
if frommerg=0 then delete;
run;
data test;
set test;
if fromwtvol = 0 then delete;
run;
data test;
set test;
drop frommerg fromwtvol;
run;

That's the code I used, I tried this before posting because I didn't want to look like a leecher but it so happens that the 2 databases i tried had nothing in common (what are the odds with 70.000 observations :D), I retried it and it works (for now!)

Thanks anyway!

Answer 2

Calling the two datasets base and mcap, and assuming that they have both been sorted by ticker and month, you can do it this way:

data want; merge base(in = b) mcap(in = m); if m & b; run;

The subsetting if will not accept any row that does not match in bath datasets.

Cheers,

Jonathan

Answer 3

agree with Jonathan... after sorting both datasets independently by ticker and time, the data step of merging is what I would use..... little modification

data want; merge base(in = b) mcap(in = m); by ticker time; if m & b; run;

records that don't have common ticker and time in both datasets would be dropped automatically..