Using SAS, I create a global macro variable called Flag that is either 0 or 1 depending on which pathway the code took. If it's 0, then only table A was created. If it's 2, A AND B were created. I then want to create a summary table. If only A exists, I need the max value of a certain variable from A. If B exists, I need the max value of the same variable from B, instead of A. My SQL code looks like this:
Proc SQL; create table WANT as
case
when &flag. = 0 then (select max(var) from A)
when &flag. = 1 then (select max(var) from B)
end as Var2
from HAVE;
The problem is I get an error message saying "table B does not exist" when Flag = 0 (which is correct, table B will not exist). I found numerous sources online that say that it should exit after the first true condition is found. Figuring that maybe it was evaluating all of the when clauses for some reason, I tried changing it to look like this:
Proc SQL; Create table WANT as select
case
when &flag. = 0 then (select max(var) from A)
else (select max(var) from B)
end as Var2;
In the above code it shouldn't even be getting to the else statement. I know for a fact that the variables and comparisons are all resolving correctly because I've tried each of them separately, and it also works fine when flag = 1. There are any number of work-arounds I could use like dummy tables, global %if-%thens, etc. I would like to be able to fix the problem within the code, but also to know if I'm understanding how SQL processes their CASE-WHEN statements.
Aucun commentaire:
Enregistrer un commentaire