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!
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 |
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 |
Thank you so much!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.