Without VAR, the SET command attempts to set a Spark session configuration instead.

DECLARE claim_year STRING;

SET VAR claim_year = (
SELECT CAST(CLAIM_YEAR - 1 AS STRING)
FROM dbengineering_prod.claimscommercial.clms_commercial_ref_years R
WHERE R.ANALYSIS_TYPE_ID = 1
AND R.YEAR_RANK = 1
);

EXECUTE IMMEDIATE
'SELECT 1
FROM `dbengineering_prod.claimscommercial.Claims_total_' || claim_year || '`
LIMIT 10';

SET Variable info-

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-aux-set-variable

You need to disambiguate this due to the shared syntax between Databricks SQL and Spark, in Spark SET is commonly used in configs.

Info on Spark Configuration 

https://spark.apache.org/docs/latest/configuration.html