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 -
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.