Search code examples
sassas-macroproc-sqlsas-studio

Referencing macro variable in a CASE WHEN statement

I have a proc sql statement which I am trying to expand to be as thorough as possible.

PROC SQL;
CREATE TABLE  &CURRENT_ID._SCR (drop = 
SDX
SPDS
CMPLV
CMPGD
Miss_Flag) AS
SELECT *,
CASE 
WHEN SDX = 1 AND CMPGD ne 0 THEN MAX(SPDS, CMPLV, CMPGD)*1.1 
WHEN SDX = 1 THEN 1

/*WHEN MISS_FLAG = 1 and &CURRENT_ID. = "MIDDLE"  THEN 30*/

WHEN MISS_FLAG = 1 THEN 100
ELSE CEIL(CMPGD)

END AS  &CURRENT_ID._SCR
FROM  &CURRENT_ID.
QUIT;

The statement runs fine until I attempt to include WHEN MISS_FLAG = 1 and &CURRENT_ID. = "MIDDLE" THEN 30

I then get errors saying all 4 of the &CURRENT_ID. values are not found in the contributing table, not just the value of "MIDDLE"

I'm not entirely sure what the issue is as the values are present in the contributing table as they are correctly found on the other &CURRENT_ID. references. The table name and column names are generated correctly using the &CURRENT_ID. references. Can anyone see what might be the issue?

Solution

  • Solved - Placing macro reference in " "

    WHEN MISS_FLAG = 1 and "&CURRENT_ID." = "MIDDLE"  THEN 30

    this correctly resolves.