How to merge two tables while giving variable values

[Mirisage]

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 ;

Format Current_date date9.;

cards;

28FEB2010  111  NPNA    10

31MAR2010  111  Current 20

31MAY2010  111  30 - 60 30

28FEB2010  444  Current 40

31MAR2010  444  30 - 60 50

30APR2010  444  30 - 60 60

31MAY2010  444  Current 70

30APR2010  555  Current 80

31MAY2010  555  NPNA    90

31AUG2011  555  90 +    12

30SEP2011  555  NPNA    0

31MAY2010  666  Current 15

31AUG2011  666  90 +    20

30SEP2011  666  Current 30

;

run;

data Have_2;

informat Post_date date9.;

Input Post_date Account_number 12-14 Net_Write_off_total_USD;

Format Post_date date9.;

cards;

31AUG2010  111  50

30SEP2011  555  77

;

run;

Question:

I need to join these 2 tables so that I could get the answer below.

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.

28FEB2010  111  NPNA    10

31MAR2010  111  Current 20

31MAY2010  111  30 - 60 30

31AUG2010  111  writoff 50

28FEB2010  444  Current 40

31MAR2010  444  30 - 60 50

30APR2010  444  30 - 60 60

31MAY2010  444  Current 70

30APR2010  555  Current 80

31MAY2010  555  NPNA    90

31AUG2011  555  90 +    12

30SEP2011  555  NPNA    0

30SEP2011  555  writoff 77

31MAY2010  666  Current 15

31AUG2011  666  90 +    20

30SEP2011  666  Current 30   

*/

My approach:

/*Transform unique ID from numeric to character*/

data have_2;

set have_2;

char_account_number = put(account_number, $7.) ;

drop account_number ;

rename char_account_number=account_number ;

run;

/*Carrying out a full outer join*/

proc sql;

            select

            a.*                     ,

            b.*

from    have_1                a full join

        have_2                      b

on a.Account_number=b.Account_number;

run;

Obviously this approach doesn’t give me what I want.

I really appreciate if someone could help me?

Thanks

Mirisage

[Ksharp · Accepted solution]

You only want union these two datasets vertically by Account group?

data Have_1;
informat Current_date date9.;
Input Current_date   Account_number $12-14 Arrears_Band $ 17-23  balance ;
Format Current_date date9.;
cards;
28FEB2010  111  NPNA    10
31MAR2010  111  Current 20
31MAY2010  111  30 - 60 30
28FEB2010  444  Current 40
31MAR2010  444  30 - 60 50
30APR2010  444  30 - 60 60
31MAY2010  444  Current 70
30APR2010  555  Current 80
31MAY2010  555  NPNA    90
31AUG2011  555  90 +    12
30SEP2011  555  NPNA    0
31MAY2010  666  Current 15
31AUG2011  666  90 +    20
30SEP2011  666  Current 30
;
run;

 

data Have_2;
informat Post_date date9.;
Input Post_date Account_number $12-14 Net_Write_off_total_USD;
Format Post_date date9.;
cards;
31AUG2010  111  50
30SEP2011  555  77
;
run;

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;

Ksharp

[art297]

Same suggestion as KSharp but, if your data is already in account_number date order, you can avoid the extra sort by including date as a 2nd by variable.  i.e.:

data want;

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

  by Account_number Current_date;

  if inb then Arrears_Band='writoff ';

run;

[Mirisage]

Hi Ksharp and Art,

Splendid! Work well. A lot of things to  learn.

Thanks again!

Mirisage