Need help to call variable inside snowflake stored procedure. It throws a “Missing column specification” – how to access variable allcols inside dynamic SQL in snowflake?

CREATE OR REPLACE PROCEDURE SP_TES()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
EXECUTE AS CALLER
AS 
$$
DECLARE 
    
    allcols VARCHAR(16777216);
 
    sql_text_test VARCHAR(16777216);
    
BEGIN

           SELECT LISTAGG( '''' || NAME || '''', ',') WITHIN GROUP (ORDER BY NAME) 
            INTO :allcols
            FROM(SELECT DISTINCT NAME FROM PARSEDNUM UNION SELECT DISTINCT NAME FROM PARSEDCHAR) 
           AS NAMES;

         sql_text_test := ' 
                CREATE OR REPLACE TEMP TABLE TEST as 
                    select
                        a.DATA_KEY_ as DATA_KEY_, 
                         ' || allcols || '
                        
                    from transposednum AS a 
                    left join transposedchar AS b
                    on a.DATA_KEY_ = b.DATA_KEY_
                
            ';
            EXECUTE IMMEDIATE sql_text_test;```
          
    RETURN 'Table  created successfully';

END;
$$;