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 -
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: