SAS MERGE to DFSORT

Musab Ahmed · New User Joined: 10 Dec 2012 Posts: 37 Location: India

I am currently assigned to a project to migrate SAS written code to DFSORT/PL1.

I came across the MERGE statement in SAS which is as follows:

MERGE inp01(in=a) inp02(in=b); by PAX_JNCD;

IF A & B THEN;process;

where inp01 and inp02 are two SAS datasets having a common variable PAX_JNCD.

To convert this to DFSORT, May i perform a JOIN on the two datasets with PAX_JNCD as a key and then reformat the matched records accordingly? Is my approach correct?

That is SAS MERGE BY can be implemented as a JOIN in DFSORT??

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

Musab Ahmed,
Please describe what you need and not provide what's present and perceived by you. Many users here may not know SAS.

Yes SAS MERGE BY can be performed by DFSort.

Robert Sample · Posted: Mon Feb 01, 2016 8:13 pm

The snippet you posted does the process when the indicated values (PAX_INCD) is the same on both files. There is no indication what happens when the value exists only on one file. DFSORT can handle such a condition via the JOIN.

vasanthz · Posted: Tue Feb 02, 2016 4:37 pm

If the SAS merge is a MANY to MANY merge then you would have problems mimicking it via DFSORT.
i.e. When you have duplicate values for key variables in both input files simultaneously.

DATA step merge does not generate a cartesian product of the merged values when it comes to MANY to MANY merges.
One symptom to diagnose this scenario is via SASLOG where there would be a NOTE saying

Musab Ahmed · New User Joined: 10 Dec 2012 Posts: 37 Location: India

Hi,

Yes I tried replicating the code with DFSORT JOIN keys concept and it gave a cartesian product which is not the case with SAS MERGE.

Need to think of some other possibility of doing this.

vasanthz · Posted: Tue Feb 02, 2016 7:23 pm

Maybe show us sample input and output files. And the scenario for merge.
Some of the DFSORT Gurus might be able to help you out.

Bill Woodger · Posted: Tue Feb 02, 2016 7:53 pm

You need to describe exactly what you want instead of a cartesian product, with, as has been mentioned, representative sample input and expected output.