lundi 8 octobre 2018

IF STATEMENT TO CREATE MACRO VARIABLE

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.

Aucun commentaire:

Enregistrer un commentaire