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 -
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.