BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

Hi all,

I have two datasets. The first one is named Accounts_In_120A and the second one is Manual_tracing. They both have debt_code as common variable. I want to merge it and I did the merge step as below. But it was giving all the information from both the tables. The first dataset (Accounts_In_120A) has only debt_code. I want all the information from the manual_tracing table to join with all the debt_code in accounts_in_120A. The merge statement is adding both the dataset. Can you suggest how to add the information from manual_trace to the first dataset using common variable debt_code?

data Adding_120A ;
  merge Accounts_In_120A  Manual_tracing ;
  by debt_code;
run;
9 REPLIES 9
PaigeMiller
Diamond | Level 26

I do not understand the problem here. You say:

 

I want all the information from the manual_tracing table to join with all the debt_code in accounts_in_120A.

 

That's what MERGE does. So ... when you have a problem where you are not getting the desired results, you need to show us portions of the input data (both tables), and you need to show us the output, and then explain what is wrong with the output.

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

Basically, with the merge statement, it is showing the debt_code from the first dataset but it is not adding the remaining information from manual_tracing dataset. If the debt_code is from the Accounts_in_120A then it will have blank columns whereas if the debt_code is from Manual_tracing then it will have the remaining coumn information. Please see the below portion of the output data of merge step.

data Test;
infile datalines dsd truncover;
input rep_code debt_code icustomerid tran_code Trace_date;
datalines;
120A 100020874 . .
120A 100417476 . .
100719376 13431155 MO9747 18JAN2023
120A 100952001 . .
100980689 4684004 MO9746 04JAN2023
10099664 159855 MO9745 09JAN2023
101041721 5184570 MO9746 11JAN2023
120A 101170710 . .;

run;

PaigeMiller
Diamond | Level 26

I still don't grasp the problem.

 

Show us portions of BOTH data sets. Show us the results you are getting. Show us the results you want.

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

Dataset - 1. accounts_in_120A

data Accounts_In_120A
infile datalines dsd truncover;
input rep_code debt_code;
datalines;
120A 307828335
120A 360588099
120A 335051207
120A 100020874
120A 307838722
120A 307843532;
run;

 

Dataset 2. Manual_Tracing

 

data Manual_Tracing
infile datalines dsd truncover;
input icustomerid debt_code tran_code Trace_date;
datalines;
13431155 100719376 MO9747 18JAN2023
4684004 100980689 MO9746 04JAN2023
159855 10099664 MO9745 09JAN2023
5184570 101041721 MO9746 11JAN2023
21818771 101320083 MO9746 20JAN2023
20673429 101760858 MO9747 17JAN2023;
run;

 

When I merge it, I can see debt_code 100417476 is having blank icustomerid, tran_code etc. The same debt_code has these values in the next row. So, I want one row of 100417476 debt_code which shows all the information from rep_code to trace_date. Here is the portion of output data when merging the dataset:

 

data Adding_120A
infile datalines dsd truncover;
input rep_code debt_code icustomerid tran_code Trace_date;
datalines;
120A 100020874 . .
120A 100417476 . .
100719376 13431155 MO9747 18JAN2023
120A 100952001 . .
100980689 4684004 MO9746 04JAN2023
10099664 159855 MO9745 09JAN2023;
run;

PaigeMiller
Diamond | Level 26

When I merge it, I can see debt_code 100417476 is having blank icustomerid, tran_code etc. 


This debt_code is not in your sample data. So I still don't see the problem. Please (this is mandatory requirement) provide data and code that illustrate the problem. Please (this is mandatory requirement) test your code before you provide it so that it runs without errors.

--
Paige Miller
Kathryn_SAS
SAS Employee

There are no debt_code values in common in either data set and the one you mention does not exist in either. I had to modify the code below to get it to run successfully. Can you run this and then explain what you are expecting to get?

data Accounts_In_120A;
input rep_code :$4. debt_code :$9.;
datalines;
120A 307828335
120A 360588099
120A 335051207
120A 100020874
120A 307838722
120A 307843532
;
run;

data Manual_Tracing;
input icustomerid :$8. debt_code :$9. tran_code :$6. Trace_date :date9.;
format trace_date date9.;
datalines;
13431155 100719376 MO9747 18JAN2023
4684004 100980689 MO9746 04JAN2023
159855 10099664 MO9745 09JAN2023
5184570 101041721 MO9746 11JAN2023
21818771 101320083 MO9746 20JAN2023
20673429 101760858 MO9747 17JAN2023
;
run;

proc sort data=accounts_in_120A;
by debt_code;
run;

proc sort data=manual_tracing;
by debt_code;
run;

data Adding_120A ;
  merge Accounts_In_120A  Manual_tracing ;
  by debt_code;
run;

proc print;
run;
Sandeep77
Lapis Lazuli | Level 10

In fact, I used Proc sql statement and added all the variables from Accounts_In_120A to manual trace and now I got all the information. Thanks.

Tom
Super User Tom
Super User

@Sandeep77 wrote:

In fact, I used Proc sql statement and added all the variables from Accounts_In_120A to manual trace and now I got all the information. Thanks.


Using PROC SQL is not going to solve the issue of debt codes not matching, unless you used some type of test other than equality in the join criteria.  But PROC SQL will allow you match multiple observations from both datasets to all of the observations for the same key values in the other dataset. 

 

If the problem was instead that you have name conflicts between some of the non-key variables in the two dataset then you can fix that in normal SAS code using the RENAME= dataset option.  For example if you have two datasets named LEFT and RIGHT that both have a key variable named ID and second variable named COMMON then you can use code like this to keep the values of both of the "common" variables by giving them names that to not conflict.

data want;
  merge left(rename=(common=common_left))
        right(rename=(common=common_right))
  ;
  by id;
run;
PaigeMiller
Diamond | Level 26

@Sandeep77  I hope you will be a good member of this community and share your solution so that others can benefit.

 

In addition, I urge you to follow the advice I gave above in all of your future posts: "Please (this is mandatory requirement) provide data and code that illustrate the problem. Please (this is mandatory requirement) test your code before you provide it so that it runs without errors." Please don't ignore this advice.

 

 

--
Paige Miller

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1199 views
  • 4 likes
  • 4 in conversation