Run string as query through macro - error parameters must precede keyword

I'm trying to accomplished this task using SAS.

I have a table PRODUCTS with columns product, date_start, query. Column query has a string with that is a query I want to run through macro and call execute.

For now, I have only one query, following:

CREATE TABLE RESULT AS 
  SELECT T1.COL1, T1.COL2, T1.COL3, SUM(T2.VALUE) AS VALUE 
  FROM TABLE1 T1 INNER JOIN TABLE2 T2
  ON T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2 
  WHERE T2.COL5 = 'A'
;

And then a I run the following code:

%MACRO RUN_QUERY(PARAM1);
    PROC SQL;
        &PARAM1
    ;QUIT;
%MEND RUN_QUERY;


DATA _NULL_;
    SET PRODUCTS;
    CALL EXECUTE('%RUN_QUERY(PARAM1='||QUERY||');');
RUN;

And I got an error:

ERROR: All positional parameters must precede keyword parameters.

What's going on? And how to solve it? I suspect it is related to dots used in the query string.

This has to do with commas in your query being passed into the function. From SAS's perspective, this is what the program is doing:

%RUN_QUERY(PARAM1=CREATE TABLE RESULT AS SELECT T1.COL1, T2.COL2, ...);

Those commas are a problem because SAS is parsing them as positional parameters. That is, SAS thinks these are individual arguments in the function:

1. CREATE TABLE RESULT AS SELECT T1.COL1
2. T2.COL2
3. T3.COL3
4. SUM(T2.VALUE) AS VALUE FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON ...

You need to quote it with a macro quoting function to tell SAS that the entire string is one argument:

%RUN_QUERY(PARAM1=%bquote(CREATE TABLE RESULT AS SELECT T1.COL1, T2.COL2, ...));

Now let's translate this into a DATA Step call execute statement. The fixed program looks like this:

DATA _NULL_;
    SET PRODUCTS;
    CALL EXECUTE('%RUN_QUERY(PARAM1=%bquote('||QUERY||'));');
RUN;

Let's test this:

data products;
    infile datalines dlm='|';
    length query $500.;
    input query$;
    datalines;
create table test as select make, model, horsepower from sashelp.cars
;
run;

%MACRO RUN_QUERY(PARAM1);
    PROC SQL;
        &PARAM1
    ;QUIT;
%MEND RUN_QUERY;


DATA _NULL_;
    SET PRODUCTS;
    CALL EXECUTE('%RUN_QUERY(PARAM1=%bquote('||QUERY||'));');
RUN;

NOTE: There were 1 observations read from the data set WORK.PRODUCTS.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: CALL EXECUTE generated line.
1         + PROC SQL;
1         + create table test as select make, model, horsepower from sashelp.cars
2         +
3         +              ;
NOTE: Table WORK.TEST created, with 428 rows and 3 columns.