Many to many merge problem with tables with different ranks

mche2550 — Wed, 12 Jul 2017 01:59:19 GMT

Dear users, I have two datasets as below and would like to perform a many-to-many like join via SAS base 9.4. Here is a subset of my datasets

Dataset1 Dataset 2 Desired result

---------------------------------------------- --------------------------------------------- ----------------------------------------

ID Date Price_return Date market_return ID Date Price_return Market_return

AAC 2/1/2007 ... 2/1/1980 ....

AAC 3/1/2007 ......

.....

AAC 31/12/2007 31/9/2016

AAE 2/1/2007

...

AAC 2/1/2008

... ...

Basically, dataset 1 is about the price return for stocks from Jan2007 to June2016 and dataset 2 is market return from 1980 to 2016. Two datasets have different ranks. Dataset 1 is sorted by ID and date and dataset 2 is sorted by date. Dataset1 has many other columns but not necessarily relevant and dataset 2 only has two columns as shown above.

Any suggestion is appreciated and many thanks in advance!

I have tried code as below but it produces zero row with 4 columns

proc sql;
create table temp as 
select a.*, b.date, b.ID, b.price_return
from dataset2 as a, dataset1 as b
where a.date=b.date;
quit;

Re: Many to many merge problem with tables with different ranks

Oligolas — Wed, 12 Jul 2017 12:18:30 GMT

Hi,

Your code is OK..you have no match on "where a.date=b.date;"

Check your data

proc sql;
   select a.name,b.name as name2
   from sashelp.class a, sashelp.class b
   where a.name eq b.name
   ;
quit;

topic Many to many merge problem with tables with different ranks in SAS Programming

Many to many merge problem with tables with different ranks

Re: Many to many merge problem with tables with different ranks