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;
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 -
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.