topic Re: How to merge two tables while giving variable values in SAS Procedures

How to merge two tables while giving variable values

Mirisage — Tue, 31 Jul 2012 03:05:01 GMT

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

Re: How to merge two tables while giving variable values

Ksharp — Tue, 31 Jul 2012 03:22:35 GMT

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

Re: How to merge two tables while giving variable values

art297 — Tue, 31 Jul 2012 04:34:55 GMT

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;

Re: How to merge two tables while giving variable values

Mirisage — Tue, 31 Jul 2012 19:34:31 GMT

Hi Ksharp and Art,

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

Thanks again!

Mirisage