Syntax error: Expected ";" but got keyword AS at [20:127] while creating a table in big query

I have a project named project_id and a dataset named dataset_id. I am trying to create separate tables sharded by the date using a for loop in BigQuery using the UI.

The BigQuery for loop is not allowing me to create different tables using the EXECUTE IMMEDIATE command. Instead I am getting an error

Syntax error: Expected ";" but got keyword AS at [20:127]

How can I correct this?

DECLARE end_of_month date;
DECLARE final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE initial_snapshot_date date DEFAULT DATE '2020-08-01';
DECLARE formatted_date STRING;

FOR date_suffix in (
  select * from UNNEST(GENERATE_DATE_ARRAY(initial_snapshot_date, final_snapshot_date, INTERVAL 1 MONTH)) date
)
DO

  SET formatted_date = FORMAT_DATE("%Y%m%d", date_suffix.date);
  SET end_of_month = LAST_DAY(date_suffix.date);
  IF 
    date_suffix.date > final_snapshot_date THEN
    LEAVE; -- kill
  END IF;
  -- create some variables, through a temporary table called tablename.
  -- this is the table we want to shard with formatted_date and save
  EXECUTE IMMEDIATE
    FORMAT("CREATE OR REPLACE TABLE `project_id.dataset_id.tablename_%s` (title STRING, publish_date INT64)", formatted_date) AS
  SELECT FORMAT(FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25")) AS formatted;
  FROM `project_id.dataset_id.base_table_1`;

END FOR;

Line 20 where the error is, is the one that says

FORMAT("CREATE OR REPLACE TABLE `project_id.dataset_id.tablename_%s` (title STRING, publish_date INT64)", formatted_date) AS
  SELECT FORMAT(FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25")) AS formatted;