topic Re: Self Join Issue in SAS Programming

Self Join Issue

agosawi — Tue, 31 Jul 2018 21:18:09 GMT

Dear SAS Users,

I have this below table.

Old Account Number	New Account Number	Date
1		20180630
1	2	20180731
2	3	20180731
3	4	20180731
4	5	20180731

Table contains both old and new account numbers.

New account numbers gets assigned whenever old gets transferred.

I want to select all those records where particular account transferred atleast 4 times in given month.
In above example I want to select all the rows from 2 for 20180731.

I tried using self joins but unfortunately it didn't work.

I found most difficult part is extracting only count of transfer ge 4.

I will really appreciate your help.

Thanks in advance!

Re: Self Join Issue

ChrisNZ — Tue, 31 Jul 2018 21:44:30 GMT

Not very elegant, but easy to understand:

data HAVE;
 input OLD NEW ;
 cards;
1 .
1 2
2 3
3 4
4 5
5 6
7 8
9 10
11 12
run;
proc sql; 
 create table FOUR_PLUS_TRANSFER_HISTORY as
 select h1.OLD as OLD1
      , h2.OLD as OLD2
      , h3.OLD as OLD3
      , h4.OLD as OLD4
      , h4.NEW AS NEW5
 from HAVE h1
        inner join
      HAVE h2
        on h1.NEW=h2.OLD
        inner join
      HAVE h3
        on h2.NEW=h3.OLD
        inner join
      HAVE h4
        on h3.NEW=h4.OLD
 where h4.NEW ne .;

 create table WANT as
 select unique HAVE.* 
 from HAVE 
     ,FOUR_PLUS_TRANSFER_HISTORY
 where OLD = OLD1 
    or OLD = OLD2 
    or OLD = OLD3 
    or OLD = OLD4 ;

quit;

OLD	NEW
1	.
1	2
2	3
3	4
4	5
5	6

Re: Self Join Issue

agosawi — Wed, 01 Aug 2018 14:06:43 GMT

Thank you so much!