topic Re: Have SAS return an error if dates are missing from the selected range in SAS Enterprise Guide

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

DME790 — Wed, 27 Jul 2016 00:07:49 GMT

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)

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

RahulG — Wed, 27 Jul 2016 03:37:35 GMT

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;

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

ballardw — Wed, 27 Jul 2016 17:53:43 GMT

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;