DATA Step, Macro, Functions and more

Many to many merge problem with tables with different ranks

Reply
User
Posts: 1

Many to many merge problem with tables with different ranks

[ Edited ]

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;

 

Frequent Contributor
Posts: 129

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

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;

 

 

________________________

- Cheers -

Ask a Question
Discussion stats
  • 1 reply
  • 43 views
  • 0 likes
  • 2 in conversation