How do I create an efficient code for subscription status for current and future periods? What will the base look like in 2, 3, 4, 5 , 6 months? The source is updated on a daily basis.
In this illustration I have 14 subscriptions with different end dates for their binding period (binding_end_date).
These 14 subscriptions can either be in
- End Of Binding (EB),
- Walkabout (WA),
- Still in binding (SB)
- Missing.
For instance, ID C3 has status EB3 (between 61-90 days left) but a month from now the status will be EB2 (see code for more info).
I have no issue with finding the current status, but how should I answer the question: What will the base look like in 2, 3, 4, 5 , 6 months?
I have created some makro variables but I’m sure there are some better solutions.😊
DATA WORK.have;
Infile datalines delimiter=',';
INPUT ID $ BINDING_END_DATE YYMMDD10.;
;
FORMAT BINDING_END_DATE YYMMDD10.;
CARDS;
A1,2021-02-22
B2,2021-03-22
C3,2021-05-10
D4,2021-06-15
E5,2021-07-09
F6,2021-07-23
G7,2021-12-08
H8,.
I9,2021-01-28
J1,2021-01-11
K2,2020-11-24
L3,2020-10-23
M4,2020-10-12
N5,2019-12-06
;
RUN;
%LET NoEBWA = INTNX ('day', today(),179,'B');
%LET EB6Upp = INTNX ('day', today(),179,'B');
%LET EB6Low = INTNX ('day', today(),150,'B');
%LET EB5Upp = INTNX ('day', today(),149,'B');
%LET EB5Low = INTNX ('day', today(),120,'B');
%LET EB4Upp = INTNX ('day', today(),119,'B');
%LET EB4Low = INTNX ('day', today(),91,'B');
%LET EB3Upp = INTNX ('day', today(),90,'B');
%LET EB3Low = INTNX ('day', today(),61,'B');
%LET EB2Upp = INTNX ('day', today(),60,'B');
%LET EB2Low = INTNX ('day', today(),31,'B');
%LET EB1_0Upp = INTNX ('day', today(),30,'B');
%LET EB1_0Low = INTNX ('day', today(),1,'B');
%LET WA0_1Upp = INTNX ('day', today(),0,'B');
%LET WA0_1Low = INTNX ('day', today(),-30,'B');
%LET WA2Upp = INTNX ('day', today(),-31,'B');
%LET WA2Low = INTNX ('day', today(),-60,'B');
%LET WA3Upp = INTNX ('day', today(),-61,'B');
%LET WA3Low = INTNX ('day', today(),-90,'B');
%LET WA4Upp = INTNX ('day', today(),-91,'B');
%LET WA4Low = INTNX ('day', today(),-120,'B');
%LET WA5Upp = INTNX ('day', today(),-121,'B');
%LET WA5Low = INTNX ('day', today(),-150,'B');
%LET WA6Upp = INTNX ('day', today(),-151,'B');
%LET WA6Low = INTNX ('day', today(),-179,'B');
%LET WA6plus = INTNX ('day', today(),-180,'B');
DATA WORK.want;
SET have;
LENGTH Status $35 EBWA $25;
FORMAT DateToday YYMMDD10.;
DateToday = Today();
DaysLeft = intck ('day', today(),BINDING_END_DATE);
IF BINDING_END_DATE > &NoEBWA THEN Status = 'SB';
/*EB6*/
ELSE IF BINDING_END_DATE <=&EB6Upp
AND BINDING_END_DATE >=&EB6low THEN Status = 'EB6';
/*EB5*/
ELSE IF BINDING_END_DATE <= &EB5Upp
AND BINDING_END_DATE >= &EB5Low THEN Status = 'EB5';
/*EB4*/
ELSE IF BINDING_END_DATE <= &EB4Upp
AND BINDING_END_DATE >= &EB4Low THEN Status = 'EB4';
/*EB3*/
ELSE IF BINDING_END_DATE <= &EB3Upp
AND BINDING_END_DATE >= &EB3Low THEN Status = 'EB3';
/*EB2*/
ELSE IF BINDING_END_DATE <= &EB2Upp
AND BINDING_END_DATE >= &EB2Low THEN Status = 'EB2';
/*EB1-WA0*/
ELSE IF BINDING_END_DATE <= &EB1_0Upp
AND BINDING_END_DATE >= &EB1_0Low THEN Status = 'EB1/0';
/*WA0-WA1*/
ELSE IF BINDING_END_DATE <= &WA0_1Upp
AND BINDING_END_DATE >= &WA0_1Low THEN Status = 'WA0/1';
/*WA2*/
ELSE IF BINDING_END_DATE <= &WA2Upp
AND BINDING_END_DATE >= &WA2Low THEN Status = 'WA2';
/*WA3*/
ELSE IF BINDING_END_DATE <= &WA3Upp
AND BINDING_END_DATE >= &WA3Low THEN Status = 'WA3';
/*WA4*/
ELSE IF BINDING_END_DATE <= &WA4Upp
AND BINDING_END_DATE >= &WA4Low THEN Status = 'WA4';
/*WA5*/
ELSE IF BINDING_END_DATE <= &WA5Upp
AND BINDING_END_DATE >= &WA5Low THEN Status = 'WA5';
/*WA6*/
ELSE IF BINDING_END_DATE <= &WA6Upp
AND BINDING_END_DATE >= &WA6Low THEN Status = 'WA5';
/*WA6+*/
ELSE IF BINDING_END_DATE <= &WA6plus AND BINDING_END_DATE NE . THEN Status = 'WA6+';
ELSE IF BINDING_END_DATE = . THEN Status = 'WA Date is missing';
ELSE Status = 'N/A';
/*EBWA*/
IF Daysleft <= 179 AND Daysleft > 0 THEN EBWA = 'EB';
ELSE IF Daysleft <= 0 THEN EBWA = 'WA';
ELSE EBWA = Status;
RUN;
