How to merge two tables while giving variable values-II

Reply
Super Contributor
Posts: 338

How to merge two tables while giving variable values-II

Hi SAS Community,

I have two tables (Have_1 and Have_2).

data Have_1;

informat Current_date date9.;

Input Current_date   Account_number $12-14 Arrears_Band $ 17-23  balance bank_number;

Format Current_date date9.;

cards;

28FEB2010  111  NPNA    10 100

31MAR2010  111  Current 20 100

31MAY2010  111  30 - 60 30 100

28FEB2010  444  Current 40 900

31MAR2010  444  30 - 60 50 900

30APR2010  444  30 - 60 60 900

31MAY2010  444  Current 70 900

30APR2010  555  Current 80 700

31MAY2010  555  NPNA    90 700

31AUG2011  555  90 +    12 700

30SEP2011  555  NPNA    0  700

31MAY2010  666  Current 15 400

31AUG2011  666  90 +    20 400

30SEP2011  666  Current 30 400

;

run;

data Have_2;

informat Post_date date9.;

Input Post_date Account_number $12-14 Net_Write_off_total_USD bank_number;

Format Post_date date9.;

cards;

31AUG2010  111  50 100

31AUG2010  111  50 200

30SEP2011  555  77 700

30SEP2011  666  77 300

;

run;

Question:

I need to join these 2 tables so that I could get the answer below. It is mandatory that I need to keep all the records in have_1 table regardless of whether the have_2 table is having corresponding matching records or not. But reverse is not mandatory, i.e. have_2 table may have excess (non matching) records, and they should not show up in final table that I want.     .    

Answer:

/*    Final data set should be like this

Notice the 2 records from have_2 table are embedded within have_1 table in date sequence. And a value called “writoff” was assigned.

Current_date  Account_number   Arrears_Band      balance     bank_number

28FEB2010  111  NPNA    10 100

31MAR2010  111  Current 20 100

31MAY2010  111  30 - 60 30 100

31AUG2010  111  writoff 50 100

28FEB2010  444  Current 40 900     

31MAR2010  444  30 - 60 50 900

30APR2010  444  30 - 60 60 900

31MAY2010  444  Current 70 900

30APR2010  555  Current 80 700

31MAY2010  555  NPNA    90 700

31AUG2011  555  90 +    12 700

30SEP2011  555  NPNA    0  700

30SEP2011  555  writoff 77 700

31MAY2010  666  Current 15 400

31AUG2011  666  90 +    20 400

30SEP2011  666  Current 30 400  

The code below (Ksharp is acknowledged) generates the wanted table to some extent but I have newly included bank_number variable to reflect business reality. Now the results are misleading us.

data want;

set have_1 have_2(in=inb  rename=(Post_date=Current_date Net_Write_off_total_USD=balance)) ;

by Account_number;

if inb then Arrears_Band='writoff ';

run;

proc sort data=want;by Account_number Current_date;run;

This is the output generated by above code.

I have striken through 2 records, and they should not be in the output. Reason is their bank_number s are not matching although account_number s are matching.

Current_date Account_number   Arrears_Band      balance     bank_number   these are heading of the output table

28FEB2010

111

NPNA

10

100

31MAR2010

111

Current

20

100

31MAY2010

111

30 - 60

30

100

31AUG2010

111

writoff

50

100

31AUG2010

111

writoff

50

200

28FEB2010

444

Current

40

900

31MAR2010

444

30 - 60

50

900

30APR2010

444

30 - 60

60

900

31MAY2010

444

Current

70

900

30APR2010

555

Current

80

700

31MAY2010

555

NPNA

90

700

31AUG2011

555

90 +

12

700

30SEP2011

555

NPNA

0

700

30SEP2011

555

writoff

77

700

31MAY2010

666

Current

15

400

31AUG2011

666

90 +

20

400

30SEP2011

666

Current

30

400

30SEP2011

666

writoff

77

300

I would appreciate any help to generate the correct table.

Thank you

Mirisage

Valued Guide
Posts: 854

Re: How to merge two tables while giving variable values-II

[ Edited ]

This gets the solution you are looking for, edited for length and rename:

 

data want;
length arrears_band $10.;
merge have_1 (in=a)
           have_2 (in=b rename=(post_date=Current_date Net_Write_off_total_USD=balance));
by account_number bank_number;
if missing(arrears_band) then Arrears_Band = 'Write Off';
run;

Ask a Question
Discussion stats
  • 1 reply
  • 157 views
  • 0 likes
  • 2 in conversation