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
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;
You didn't make a want dataset so I leave you to check this. I think this will work, although you may need to tweak it a day or so (or by a month) one way or the other, .
proc format;
value EBs_WAs
6<-high = 'SB'
5 = 'EB6'
4 = 'EB5'
3 = 'EB4'
2 = 'EB3'
1 = 'EB2'
0 = 'EB1/0'
-1 = 'WA0/1'
-2 = 'WA2'
-3 = 'WA3'
-4 = 'WA4'
-5 = 'WA5'
low-<-6 = 'WA6+'
;
run;
DATA want;
SET have;
LENGTH Status $35 EBWA $25;
FORMAT DateToday YYMMDD10.;
DateToday = Today();
DaysLeft = intck ('day30', today(),BINDING_END_DATE);
DaysLeft2=DaysLeft ;
format DaysLeft2 EBs_WAs.;
length DaysLeft3 $5;
DaysLeft3=put(DaysLeft,EBs_WAs.);
RUN;
Thanks! I'll test it 😀
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.