topic Re: Proc Fedsql - Date and Macro variable resolution in SAS Programming

Proc Fedsql - Date and Macro variable resolution

phopkinson — Mon, 18 Nov 2024 15:40:21 GMT

Hi,

I have a piece of code which selects the date between two macro variables (Var1 and Var2) - however in proc fedsql these don't seem to resolve within the %TSLIT() function to a comparable date value. Is there a way to get them to work in my where clause?

Thanks in advance.

Re: Proc Fedsql - Date and Macro variable resolution

ballardw — Mon, 18 Nov 2024 15:57:49 GMT

Can you share the code that generates the two macro variables? And maybe provide some examples that "don't work". And the subsequent call to %TSLIT.

A very common problem with macro variables and "date" values is applying a format to the value and then attempting to use the result in a comparison or calculation. Generally the formatted values don't do well because the behave as character values and things like 11/05/2024 don't evaluate well when compared to 01/05/2025 (as in the first is "larger" than the second because of the rules used for comparison of strings). OR 11/05/2024 gets treated as multiple division operations as it would here and is why x = 0.0010869565 (more or less)

data junk;
   x = 11/05/2024;
run;

Re: Proc Fedsql - Date and Macro variable resolution

PaigeMiller — Mon, 18 Nov 2024 15:48:54 GMT

SHOW US the code.

Since you have macro variables, turn on the macro debugging options, run the code again and then also show us the log.

options mprint symbolgen;

Re: Proc Fedsql - Date and Macro variable resolution

phopkinson — Mon, 18 Nov 2024 15:56:52 GMT

The code that generates the macro variables is completely fine and not the cause of my issue - I can't share that part anyway I'm afraid. This is the where condition which I am certain is causing the problem.

WHERE %TSLIT(&VAR1.) <= date <= %TSLIT(&VAR2.);

This is the error message that I get in the log.

ERROR: Operator does not exist: bool <= CHAR

Re: Proc Fedsql - Date and Macro variable resolution

PaigeMiller — Mon, 18 Nov 2024 16:05:37 GMT

What are the values of &VAR1 and &VAR2? What are the typical values of variable DATE, un-formatted? What format is assigned to variable DATE? Is DATE numeric or character?

Re: Proc Fedsql - Date and Macro variable resolution

phopkinson — Mon, 18 Nov 2024 16:32:51 GMT

It seems to be after the %TSLIT macro function is called it converts the date from date9 to character.

Re: Proc Fedsql - Date and Macro variable resolution

Quentin — Mon, 18 Nov 2024 16:39:42 GMT

Is your FEDSQL sending the code off to POSTGRES? It looks like a Postgres error message. See e.g. this thread:
https://communities.sas.com/t5/SAS-Programming/Error-in-PROC-SQL-query/td-p/859565

Just to remove the macro factor, try writing your code with hard coded values, and I expect you'll get the same error message, e.g.:

WHERE '2024-01-01' <= date <= '2024-12-31';

Also, that syntax of chained operators, x<y<z is allowed in SAS, but is not common in other languages, so I would try:

WHERE ('2024-01-01' <= date) and (date <= '2024-12-31') ;

The error message says you're comparing a boolean value (true false) to a character value, which isn't allowed in Postgres. That would happen if postgres isn't evaluating x<y<z as a single boolean expression. Instead it's doing (x<y)<z.

Then once you get the code working without %TSLIT, go back to trying to use %TSLIT to add the single quotes.

Re: Proc Fedsql - Date and Macro variable resolution

Tom — Mon, 18 Nov 2024 16:35:26 GMT

Looks like it complaining about the operators, not the values.

Are you sure that PROC FEDSQL supports SAS's abbreviated comparison syntax that treats:

a op1 b op2

as meaning

(a op1 b) and (b op2 c)

What happens if you spell things out for FEDSQL?

WHERE (%TSLIT(&VAR1.) <= date) and (date <= %TSLIT(&VAR2.));

Re: Proc Fedsql - Date and Macro variable resolution

phopkinson — Mon, 18 Nov 2024 16:38:30 GMT

You were right it was the use of the chained operators that caused the problem. Thank you very much for your help.