CODEWITHSUNDEEP
sqlif-statementsassas-macro
hadoo
hadoo

Reputation: 213

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.

Upvotes: 1

Views: 90

Answers (2)

Tom
Tom

Reputation: 51566

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;

Upvotes: 2

hadoo
hadoo

Reputation: 213

ok, i figured it out. The case statement needed additional quotes since it was in a macro.

PROC SQL NOPRINT;
SELECT case when compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") ="'.'" then 
"'10oct2018'" else compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") end
into :start_date 
FROM table.test
;

Upvotes: 0

Related Questions