Date:         Tue, 23 Jan 2001 15:52:00 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SUBSTR, TRIM, LEFT solution
Comments: To: "PAdkins@CHECKFREE.COM" <PAdkins@CHECKFREE.COM>
Content-Type: text/plain; charset="iso-8859-1"

Is the MERGE dataset list commutative?  No.  Let me see if I can remember a
counterexample....

If dataset A contains a variable with the same name as a variable in dataset
B, and the BY group used in the MERGE does not include that variable, the
result of the MERGE contains the value of the variable in the dataset on the
RHS.

To avoid this trap, add KEEP= options to MERGE's to keep non-key variable
names distinct.  (Also a good idea to give distinct aliases to duplicate
columns names in results of SQL joins.)

But also watch out for the NODUPKEY option of PROC SORT.  You will find some
horror stories on SAS-L from some who have assumed that it removes
duplicates intelligently.  Using PROC FREQ and PROC PRINT to verify results
seems a good first Q/C measure.  Sig

-----Original Message-----
From: Paula M. Adkins [mailto:PAdkins@CHECKFREE.COM]
Sent: Tuesday, January 23, 2001 2:59 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SUBSTR, TRIM, LEFT solution


SAS MVS-Beginner:

First of all, thanks for all the individual responses. I think I have the
syntax straight for SUBSTR. I didn't have to use it though in my earlier
example, I just changed the infile length.

I was able to merge Data Set A to Data Set B by changing the infile length
in variable X from 11 to 9 to match variable X in Data Set B. I ran a PROC
FREQ and a PROC PRINT to ensure my data was in order.  At first it didn't
work but after I added NODUPKEY to the PROC SORT it merged fine. The first
dataset was 1.1 million records and the second one was 1.0 million.

I have another question, does it matter the order when you complete a
merge?  Merge A to B or B to A? Do you get different results?

data c;
merge a b
    by id_num;
run;

OR

data c:
merge b a
    by id_num;
run;

Paula M. Adkins, Risk Analyst I
Payment Systems Department
CheckFree Corporation
6000 Perimeter Drive
Dublin, OH 43017
614-564-3117, 614-564-4308-fax
www.checkfree.com