topic Re: Merge Question in SAS Procedures

Merge Question

KevinC_ — Thu, 10 Dec 2009 22:44:54 GMT

Hello everyone,

I am combining a series of files using merges. One of the fields (first_prin_bal) gets lost halfway thru the merges. Any input will be greatly appreciated.

Thank you!

Here is my code:

(first_prin_bal is in the dataset hafc_input2)

data wk_rpt1;
merge wk_rpt (in=a) hafc_input2 (in=b);
by loan_no;
if a;
run;

(* now i select 3 loan_nos and print their first_prin_bal *)

select loan_no, first_prin_bal
from wk_rpt1
where loan_no in ('0010136695', '0012754990', '0015171150');
run;

First_Prin_
loan_no Bal
------------------------
0010136695 73607.66
0012754990 62552.84
0015171150 98799.29

data hafc_mail_wf_nov30;
merge hafc_mail_temp(in=a) wk_rpt1 (in=b) cont_hist1(in=c) ;
by loan_no;
if b;
if in_h_wf = 1 or in_map_wf = 1 then workflow = 'Y';
else workflow = 'N';
run;

( now if i try to print the first_prin_bal for the same 3 loan_nos they aren't there anymore )

proc sql;
select loan_no, first_prin_bal
from hafc_mail_wf_nov30
where loan_no in ('0010136695', '0012754990', '0015171150');
run;

loan_no First_Prin_Bal
---------------------------------
0010136695 .
0012754990 .
0015171150 .

I think its obvious they got lost in the second merge. Is it because i have 'if in b'? How do i keep this field in the output?

Thank you!

Re: Merge Question

sbb — Fri, 11 Dec 2009 01:04:21 GMT

I have to say that your code pieces are working with different input files (something maybe you overlooked?) - you need to verify each file that feeds the MERGE process using PROC PRINT or FREQ (and CONTENTS for that matter) using a WHERE (each individually prior to the MERGE) to confirm that expected input conditions are actually suitable for a merge process.

Scott Barry
SBBWorks, Inc.

Re: Merge Question

KevinC_ — Fri, 11 Dec 2009 01:34:19 GMT

Thank you Scott.

To rephrase my problem:

merge file1 (in=a) file2 (in=b)
if a;

this should keep every field from file1 in the resultant file, right?
But it's not. a field in file1 is missing in the output file. This is where I m confused..

Thankds.

Re: Merge Question

sbb — Fri, 11 Dec 2009 04:49:42 GMT

Suggest you add the following SAS DATA step diagnostic stmt to identify conditions with your merge process - likely you have a BY variable that is not contributing as you expect:

PUTLOG '>DIAG-nn>' / _ALL_;

where "nn" identifies a unique value when you have more than one of these statements in your SAS program at key points in the processing. Likely for you there would only be one for the over-simplified DATA step you demonstrated.

Also, highly suggest you read up on MERGE with BY statement processing and how SAS deals with contributing observations, variables and values -- both character and numeric. Also, removing any output format, if declared, may help you detect a difference in an observation with another.

Scott Barry
SBBWorks, Inc.

Re: Merge Question

Patrick — Fri, 11 Dec 2009 09:39:28 GMT

The resulting data set will actually contain all fields from all contributing tables.

Where you must be very careful is when the same field exists in more than one table (something which might be the cause of what you observe).

As proposed: It's worth reading how the merge works exactly because there are some differences to an SQL join. That's a trap you can step in or you can take advantage of the differences depending on the case you have to solve.

Re: Merge Question

Peter_C — Fri, 11 Dec 2009 12:42:12 GMT

KevinC

beware a feature of data set options!
Any variable you name as the value of a statement option, like ( in= variable ) on a set or merge, will be dropped - it cannot be "keep"-ed.
So, make sure the in= variable does not have the same name as any variable among your inputs, that you want to keep!

good luck
PeterC

Re: Merge Question

abdullala — Fri, 11 Dec 2009 16:20:54 GMT

does the dataset cont_hist1 also contain the variable first_prin_bal, and with values missing for these 3 loan_no?

Re: Merge Question

KevinC_ — Fri, 11 Dec 2009 21:12:07 GMT

Thank you everyone for your valuable iniput! I really appreciate it.

I ran the same program this morning from the top and it worked.. I didn't change the code at all... not sure why or what happened.

But I will do what some of you suggested and read up on MERGE. Thank you again for your help!