IF statement to create macro variable

Question

I am attempting to create a macro variable based on the value contained in an outside table. I'm currently using this:

PROC SQL NOPRINT;
SELECT compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'")
into :start_date 
FROM table.test
;

This provides me the max date from my table as '10OCT2018' The problem is that when I initially run this my source table will not have a max value as it will be blank so it evaluates to '.' I'd like to do something like:

PROC SQL NOPRINT;
SELECT IF compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") ='.' THEN 
'10OCT2018' ELSE compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") END
into :start_date 
FROM table.test
;

This would allow me to fill the variable when the source table is empty but then make use of the max date after it has been updated with data.

Answer 1

Just test the original value instead of the formatted value in your WHEN clause. Instead of hardcoding a default date use the today() function to get a value to use when the max() value is missing (or null in SQL speak). The quote() function can add the quotes.

If the value is coming from a remote database then use a nested query to generate the max value first and then format it. That will make sure that only the max value is pulled from the remote database.

proc sql noprint;
select quote(put(
   case when max_call_date is null then today() else max_call_date+1 end
   ,date9.),"'")
  into :start_date 
  from (select max(call_date) as max_call_date from table.test)
;
quit;