Desktop productivity for business analysts and programmers

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

Reply
Contributor
Posts: 48

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

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)

Regular Contributor
Posts: 236

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

 

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;
 

 

Grand Advisor
Posts: 10,241

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

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;
Ask a Question
Discussion stats
  • 2 replies
  • 229 views
  • 3 likes
  • 3 in conversation