Why cannot I merge these two tables?

[Mirisage]

Hi SAS Forum,

I have been trying to merge the attahced two tables for hours using the follwoing code but didn't work.

Book1 dataset is having a single record which is not taken into the final mereged file called "COMBINED_file" despite the fact its bank_number and account_number

are identically found in book2 talbe as well.

Could any help me?

Thanks

Mirisage

proc sort data=A.BOOK1 out=file_A;

   by bank_number Account_number ;

run;

proc sort data=A.BOOK2  Out=file_B;

     by bank_number account_number ;

run;

data COMBINED_file;

  merge file_A (IN=a)

        file_B (IN=b)

           ;

by bank_number account_number ;

if a and b;

run;

[Haikuo]

I am not sure if I understand what you are after. You code behaved the way it should. If you want to stack them, then use 'set' to replace 'merge', or if you don't want the variable from file_a being replace by file_b, then switch their order in the merge statement:

merge file_b(in=b) file_a(in=a);

or rename the variable names.

HTH,

Haikuo

[Mirisage]

Hi Haikuo,

Thanks.

Sorry for the confusion.

The final merged table that I want should look like below table. It should comprise all 5 records of Book2 data set and the single record of Book1 data set.

Please see the bottom-most row that I have bolded in below table. It is the single record that belongs to Book1  data set.

My merging code returns into the final merged table only the five records that belong to Book2 data set. And my code is not stacking the single record belongs to Book1 data set into the final merged table.

Bank_number	Current_date	Account_number	Short_name	Currency_code	Product_code	Currency_short_name	Country	Transit	Balance	Product	Arrears_Band
10	28-Feb-10	1000000000	OOOOOOOOO RRRRRRRR	0	1	VVV	DDDDDDDD	Retail	1700	Personal Loan	NPNA
10	31-Mar-10	1000000000	OOOOOOOOO RRRRRRRR	0	1	VVV	DDDDDDDD	Retail	1700	Personal Loan	NPNA
10	30-Apr-10	1000000000	OOOOOOOOO RRRRRRRR	0	1	VVV	DDDDDDDD	Retail	1700	Personal Loan	NPNA
10	31-May-10	1000000000	OOOOOOOOO RRRRRRRR	0	1	VVV	DDDDDDDD	Retail	1700	Personal Loan	NPNA
10	30-Jun-10	1000000000	OOOOOOOOO RRRRRRRR	0	1	VVV	DDDDDDDD	Retail	1700	Personal Loan	NPNA
10	6-Jul-10	1000000000	missing	missing	missing	missing	missing	missing	2216	Personal Loan	writoff

[AUTigers · Accepted solution]

data COMBINED_file;

  set file_B

      file_A

           ;

by bank_number account_number ;

run;