How do I create an efficient code for subscription status for current and future periods?

Pili1100 — Thu, 18 Feb 2021 18:40:26 GMT

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;

Re: How do I create an efficient code for subscription status for current and future periods?

PhilC — Thu, 18 Feb 2021 21:07:50 GMT

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;

Re: How do I create an efficient code for subscription status for current and future periods?

Pili1100 — Thu, 18 Feb 2021 21:27:50 GMT

Thanks! I'll test it 😀

topic Re: How do I create an efficient code for subscription status for current and future periods? in SAS Programming

How do I create an efficient code for subscription status for current and future periods?

Re: How do I create an efficient code for subscription status for current and future periods?

Re: How do I create an efficient code for subscription status for current and future periods?