BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6
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!
7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
KevinC_
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Patrick
Opal | Level 21
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.
Peter_C
Rhodochrosite | Level 12
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
abdullala
Calcite | Level 5
does the dataset cont_hist1 also contain the variable first_prin_bal, and with values missing for these 3 loan_no?
KevinC_
Fluorite | Level 6
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 814 views
  • 0 likes
  • 5 in conversation