Here is the file @Kurt_Bremser DATA WORK.INVENTORY;
LENGTH
MonthYear 8
Product_grp $ 7
accts 8
cycle 8 ;
FORMAT
MonthYear mmyys7.
Product_grp $CHAR7.
accts BEST12.
cycle BEST12. ;
INFORMAT
MonthYear DATE9.
Product_grp $CHAR7.
accts BEST12.
cycle BEST12. ;
INPUT
MonthYear : ANYDTDTE10.
Product_grp : $CHAR7.
accts : BEST32.
cycle : BEST32. ;
datalines;
30/11/2017 card 1234567 0
30/11/2017 card 2345678 0
30/11/2017 card 3456789 0
30/11/2017 card 4567891 1
30/11/2017 card 5678912 1
30/11/2017 card 6789123 1
30/11/2017 card 7891234 2
30/11/2017 card 8912345 2
30/11/2017 card 9123456 2
31/12/2017 card 4152631 3
31/12/2017 card 1425361 3
31/12/2017 card 7485961 1
31/12/2017 card 4758691 2
31/12/2017 card 2536141 3
31/12/2017 card 5869581 1
31/12/2017 card 4758361 2
31/12/2017 card 2547961 3
31/12/2017 card 6385741 4
31/12/2017 card 9685147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9689147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9644147 2
31/01/2018 lines 9686847 3
31/01/2018 lines 9678947 3
31/01/2018 lines 9682227 3
31/01/2018 lines 9888147 3
31/01/2018 lines 9684677 3
31/01/2018 lines 9685987 5
28/02/2018 lines 9687857 6
28/02/2018 lines 9687957 0
28/02/2018 lines 9687897 1
28/02/2018 lines 9663647 3
28/02/2018 lines 9688897 1
28/02/2018 lines 4152781 2
28/02/2018 lines 4152572 3
28/02/2018 lines 4152573 1
28/02/2018 lines 4152574 2
28/02/2018 lines 4152575 4
31/03/2018 lines 4152576 5
31/03/2018 lines 4152577 4
31/03/2018 lines 4152578 7
31/03/2018 lines 4152571 5
31/03/2018 lines 4152572 6
31/03/2018 lines 4152573 4
31/03/2018 lines 4152574 6
31/03/2018 lines 4152575 5
;
RUN;
DATA WORK.Delinquency_hist;
LENGTH
acct_dql 8
Cycle_dql 8
Start_date 8
End_date 8 ;
FORMAT
acct_dql BEST12.
Cycle_dql BEST12.
Start_date DATETIME21.2
End_date DATETIME21.2 ;
INFORMAT
acct_dql BEST12.
Cycle_dql BEST12.
Start_date DATETIME21.
End_date DATETIME21. ;
INPUT
acct_dql : BEST32.
Cycle_dql : BEST32.
Start_date : ANYDTDTM18.
End_date : ANYDTDTM18. ;
datalines;
1234567 1 05DEC2019:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2019:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2019:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2019:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2019:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2019:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2019:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2019:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2019:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2019:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2019:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2019:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2019:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2019:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2019:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2019:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2019:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2019:00:00:00 16JAN2020:00:00:00
9685147 0 19DEC2019:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2019:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2019:00:00:00 31DEC2019:00:00:00
2536141 0 25DEC2019:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2019:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2019:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2019:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2019:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2019:00:00:00 14DEC2019:00:00:00
9687897 2 30NOV2019:00:00:00 24DEC2019:00:00:00
9663647 2 25DEC2019:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2019:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2019:00:00:00 11DEC2019:00:00:00
4152631 5 28DEC2019:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2019:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2019:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2019:00:00:00 18DEC2019:00:00:00
4152573 1 28DEC2019:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 3 19DEC2019:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2019:00:00:00 08JAN2020:00:00:00
4152577 2 19DEC2019:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2019:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2019:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2019:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2019:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2019:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2019:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2019:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2019:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2019:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2019:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2019:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2019:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2019:00:00:00 14DEC2019:00:00:00
2547961 2 25DEC2019:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2019:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2019:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2019:00:00:00 06APR2019:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2019:00:00:00 08AUG2019:00:00:00
1425361 1 02FEB2019:00:00:00 06MAR2019:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 19APR2018:00:00:00 19MAY2018:00:00:00
2547961 1 14MAR2019:00:00:00 12APR2019:00:00:00
6385741 0 19DEC2018:00:00:00 18JAN2019:00:00:00
9685147 2 19JUL2019:00:00:00 20AUG2019:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2019:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2019:00:00:00 04JUL2019:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2019:00:00:00 27AUG2019:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2019:00:00:00 26JUN2019:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
1234567 1 05DEC2017:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2017:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2017:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2017:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2017:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2017:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2017:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2017:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2017:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2017:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2017:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2017:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2017:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2017:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2017:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2017:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2017:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2017:00:00:00 16JAN2020:00:00:00
9685147 0 17DEC2017:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2017:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2017:00:00:00 31DEC2017:00:00:00
2536141 0 25DEC2017:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2017:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2017:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2017:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2017:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2017:00:00:00 14DEC2017:00:00:00
9687897 2 30NOV2017:00:00:00 24DEC2017:00:00:00
9663647 2 25DEC2017:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2017:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2017:00:00:00 11DEC2017:00:00:00
4152631 5 28DEC2017:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2017:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2017:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2017:00:00:00 18DEC2017:00:00:00
4152573 1 28DEC2017:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 3 17DEC2017:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2017:00:00:00 08JAN2020:00:00:00
4152577 2 17DEC2017:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2017:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2017:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2017:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2017:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2017:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2017:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2017:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2017:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2017:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2017:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2017:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2017:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2017:00:00:00 14DEC2017:00:00:00
2547961 2 25DEC2017:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2017:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2017:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2017:00:00:00 06APR2017:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2017:00:00:00 08AUG2017:00:00:00
1425361 1 02FEB2017:00:00:00 06MAR2017:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 17APR2018:00:00:00 17MAY2018:00:00:00
2547961 1 14MAR2017:00:00:00 12APR2017:00:00:00
6385741 0 17DEC2018:00:00:00 18JAN2017:00:00:00
9685147 2 17JUL2017:00:00:00 20AUG2017:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2017:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2017:00:00:00 04JUL2017:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2017:00:00:00 27AUG2017:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2017:00:00:00 26JUN2017:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
9689147 4 29DEC2017:00:00:00 17JAN2018:00:00:00
9687147 4 30JAN2018:00:00:00 01MAR2018:00:00:00
9644147 2 28MAY2017:00:00:00 26JUN2017:00:00:00
9686847 1 29DEC2017:00:00:00 13JAN2018:00:00:00
9678947 1 29DEC2017:00:00:00 03JAN2018:00:00:00
9682227 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9888147 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9684677 0 05DEC2017:00:00:00 04JAN2020:00:00:00
9685987 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9687857 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9687957 1 29DEC2017:00:00:00 03JAN2018:00:00:00
;
RUN;
PROC SQL;
CREATE TABLE WORK.Dec2017 AS
SELECT t1.MonthYear,
t1.Product_grp,
t1.cycle AS 'Current Cycle'n,
t2.Cycle_dql AS 'Previous Cycle'n,
/* Account Count */
(COUNT(t2.Cycle_dql)) AS 'Account Count'n
FROM WORK.INVENTORY t1
LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
WHERE t1.MonthYear = '31Dec2017'd AND (1827619200) >= t2.Start_date AND (1827619200) < t2.End_date
GROUP BY t1.MonthYear,
t1.Product_grp,
t1.cycle,
t2.Cycle_dql
ORDER BY t2.Cycle_dql,
t1.cycle;
QUIT;
PROC SQL;
CREATE TABLE WORK.Jan2018 AS
SELECT t1.MonthYear,
t1.Product_grp,
t1.cycle AS 'Current Cycle'n,
t2.Cycle_dql AS 'Previous Cycle'n,
/* Account Count */
(COUNT(t2.Cycle_dql)) AS 'Account Count'n
FROM WORK.INVENTORY t1
LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
WHERE t1.MonthYear = '31Jan2018'd AND (1830297600) >= t2.Start_date AND (1830297600) < t2.End_date
GROUP BY t1.MonthYear,
t1.Product_grp,
t1.cycle,
t2.Cycle_dql
ORDER BY t2.Cycle_dql,
t1.cycle;
QUIT;
PROC SQL;
CREATE TABLE WORK.Final_Table AS
SELECT * FROM WORK.DEC2017
OUTER UNION CORR
SELECT * FROM WORK.JAN2018
;
Quit;
... View more