Hi all,
I have 2 datasets:
1. contains approximately 25 million observations of 5 variables: MutualfundID, year, month, assetID and holdings. Each month of each year in my dataset it shows what the holdings of these funds in multiple assets are. So this contains several observations fora specfic mutualfund in a specific month; because the have positions in several assets.
2. contains 250.000 observations of 4 variables. Also: MutualfundID, year, month and then one other var: netfundflows. So this contains only 1 observation for a specific fund in a specific month because in shows net flows.
What I want to accomplish is that those fundflows come in as a 6th variable in the first dataset. Because each fund has multiple assets each month, the net fund flows will be repeated several times in that month because it relates to the fund itself and not to the asset.
I tried:
data merged;
merge dataset1 dataset2;
by MutualfundID, year, month;
run;
But this doesnt give me the right results. How should I approach this?
proc sql;
create table merged as
select
t1.*,
t2.netfundflows
from
dataset1 t1
left outer join dataset2 t2
on t1.mutufalfundid=t2.mutualfundid
and t1.year=t2.year
and t1.month=t2.month;
quit;
Thanks DBailey, will try it asap!
Hi,
Just as information, and to keep your datastep example, you migt use the following code (which makes the same as the PROC SQL provided by DBailey). As long as the observations exist in the dataset1, they will appear in the merged dataset (with empty values in the fields normally coming from the dataset2).
data merged;
merge dataset1 (in = inDS1)
dataset2 (in = inDS2);
by MutualfundID year month;
if inDS1;
run;
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: