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.

case when compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") ='.' then '10oct2018' else compress("'" || put(MAX(CALL_DATE)+1 , DATE9.)|| "'") end while it does not error does not evaluate the way I would think though. — hadoo
– hadoo, Commented Oct 8, 2018 at 14:32

Tom · Accepted Answer · 2018-10-08 16:24:50Z

2

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;

edited Oct 8, 2018 at 16:24

answered Oct 8, 2018 at 16:04

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hadoo Over a year ago

this works! i have never seen the quote function before. From what i gather it is adding double " and then it looks like it is changing it to single quote. the reason i hard coded the date has to do with the scheduling i setup. i have a blank table setup and then scheduled to run on a certain date. this is cleaner than the solution i noted below and i learned a new function. thank you.

hadoo · Accepted Answer · 2018-10-08 14:39:51Z

0

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
;

answered Oct 8, 2018 at 14:39

hadoo

2133 silver badges13 bronze badges

Collectives™ on Stack Overflow

IF statement to create macro variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related