topic Re: Merging Multiple Datasets in SAS Programming

Merging Multiple Datasets

monalisa — Wed, 31 Aug 2016 10:17:30 GMT

I have a below sample SAS query which i need to convert ot SQl.

Can any one please help me in understanding the query, which is:

Merge Dataset1 (In=data1 keep col1, col2....)

DataSet2

DataSet3

Dataset4

By id;

if data1=1

%ran

Re: Merging Multiple Datasets

RW9 — Wed, 31 Aug 2016 10:22:46 GMT

Hi,

Not much I could really add to what is in the documentation:

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001304305.htm

At a quick look, the one I would start with is A full join B on A.ID=B.ID.

SQL joins are:

Re: Merging Multiple Datasets

Reeza — Wed, 31 Aug 2016 10:46:34 GMT

What's in %ran?

Dataset1 is left joined with 2,3,4 on key field ID.

Re: Merging Multiple Datasets

monalisa — Wed, 31 Aug 2016 12:27:55 GMT

Am new to SAS. What i understood till now is SAS merges 2 dataset at a time (not sure though). If this is the case, then here Dataset1 left join dataset2, then output left join dataset3 and so on.Is this correct?

Re: Merging Multiple Datasets

Reeza — Wed, 31 Aug 2016 13:05:15 GMT

@monalisa wrote:

Am new to SAS. What i understood till now is SAS merges 2 dataset at a time (not sure though). If this is the case, then here Dataset1 left join dataset2, then output left join dataset3 and so on.Is this correct?

No, I don't think that's accurate.

If you have multiple IDs in each dataset you won't get the results you expect compared to SQL, ie many to many match.

The IF IN statement is what's used to filter the join and create a left join.

Your best bet is to create a sample dataset and compare the results between the two Procs until you get your desired result.