Have SAS return an error if dates are missing from the selected range

[DME790]

Hi,

In am using a date prompt to select a range of dates and want the ability for SAS to return an error if a date is missing from the date range.

example: selected date range 20Jul2016 to 24Jul2016 and the data for 24Jul2016 is missing

Data Work.SSW;
	Set Cartel.preagent_telephony;
	Keep EVENT_DT PROGRAMME_NM SERVICE_NM CLUSTER CALLS_ANSWERED_CNT
		CALLS_ABANDONED_CNT TOTAL_ANSWER_TM MBL;
	Where Event_dt Between "&Week_Prompt_MIN."d and "&Week_Prompt_MAX."d
		and CALL_TYPE = 'External'
		and PROGRAMME_NM IN ("1.1 SSW");

	IF Cluster_ID in ('1' , '6', '9', '13', '18', '27') and Queue_Type IN ('A', 'B') THEN
		MBL = 'MBL';
	ELSE MBL = 'Other';
Run;

 Cheers

Dean  (SAS 7.11)

[RahulG]

I have created a work dataset and comparing if any date is missing;

If date missing then give error message and abort.

Data work.DATE_RANGE;
format Event_dt date9.;
do Event_dt = "&Week_Prompt_MIN."d TO "&Week_Prompt_MAX."d
OUTPUT;
end;
RUN;

proc sort data=work.SSW ; by Event_dt; run;

Data Work.SSW; 
MERGE Cartel.preagent_telephony(IN=A) WORK.DATE_RANGE(IN=B);
BY Event_dt ; 
Keep EVENT_DT PROGRAMME_NM SERVICE_NM CLUSTER CALLS_ANSWERED_CNT 
CALLS_ABANDONED_CNT TOTAL_ANSWER_TM MBL; 

Where Event_dt Between "&Week_Prompt_MIN."d and "&Week_Prompt_MAX."d
 and CALL_TYPE = 'External' and PROGRAMME_NM IN ("1.1 SSW"); 

IF B AND NOT A THEN
 DO; 
PUT "ERROR: DATE RANGE MISSING"; 
ABORT CANCEL; 
END; 


IF Cluster_ID in ('1' , '6', '9', '13', '18', '27')
and Queue_Type IN ('A', 'B') 
THEN MBL = 'MBL'; ELSE MBL = 'Other'; 

Run;

[ballardw]

An alternate approach to test before the data step and create a macro variable that could be used for program flow.

proc sql noprint;
   select case 
            when (("&Week_Prompt_MAX."d - "&Week_Prompt_MIN."d)+1) - count(*) >0 then 'Missing'
            else 'Present'
          end as DateCompare INTO : DateCompare
   from (select distinct Event_dt from Cartel.preagent_telephony
         where Event_dt Between "&Week_Prompt_MIN."d and "&Week_Prompt_MAX."d
               and CALL_TYPE = 'External'
		         and PROGRAMME_NM IN ("1.1 SSW")
         )
;
quit;