BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

3 REPLIES 3
Ksharp
Super User

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
Opal | Level 21

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
Obsidian | Level 7

Hi Ksharp and Art,

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

Thanks again!

Mirisage

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 734 views
  • 3 likes
  • 3 in conversation