topic Re: Problem with merging 2 datasets in SAS Programming

Problem with merging 2 datasets

DaanUtrecht — Thu, 07 Mar 2013 15:24:18 GMT

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?

Re: Problem with merging 2 datasets

DBailey — Thu, 07 Mar 2013 16:06:23 GMT

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;

Re: Problem with merging 2 datasets

DaanUtrecht — Thu, 07 Mar 2013 16:38:27 GMT

Thanks DBailey, will try it asap!

Re: Problem with merging 2 datasets

Florent — Thu, 07 Mar 2013 17:11:49 GMT

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;