Sorry for the delay, but this worked beautifully.  Thank you! ... View more

So when manually writing the code out like below, the program runs.   WHERE source = ANY('{src1,src2,src3,src4}'::varchar[])   However, I will not always have the same "Source" values or the same number of sources.  If a multi-selection prompt is used to create multiple macro variables, and I use those create a single macro to populate the values that I need between the { } in the above statement, it either populates an empty table, or it shoots me an error stating, "ERROR:  CLI prepare error:  [SAS] [ODBC Greenplum Wire Protocal driver][Greenplum]ERROR:   column "{src1,src2,src3,src4}" does not exist."   The code below generates a value that should work:   %let _pre=%str(ANY%(%'{); /* ***%let varlist=src1,src2,src3,src4;  *** THIS IS CREATED BELOW */  %let _post=%str(}%'::varchar[]%)) ; %let _out= %unquote(&_pre.&varlist.&_post); /* MACROS BELOW ARE GENERATED BY MULTI-SELECTION PROMPT */ %let match_src1 = src1 %let match_src2 = src2 %let match_src3 = src3 %let match_src4 = src4 /* PROMPT END */   data _null_;  length varList $1000;  do i=1 to &match_src_count.;   if i=1 then do;    varList=catx(',', varList, symget("match_src"));   end;   else do;    varList=catx(',', varList, symget(cats("match_src", i)));   end;  end;  call symputx('varList', varList); run;   %put &varList.; /* *** RESULTS according to the macro dictionary: varList = src1,src2,src3,src4 *** */   proc sql; CONNECT TO GREENPLM AS gpcon (server="gp-db" db=db port=1 user=&Enter_Username password=&Enter_Password); CREATE TABLE test AS  (  SELECT * FROM connection to gpcon   (select *      FROM  data.prim_tbl   WHERE id in      (select id       from &sep_tbl.)   AND record_ts::date <= &endDate.   AND source = &_out. ) ); QUIT;   The results of the program return the table with only the column headers.  There are no errors or notes stating anything did not run properly.  The marco dictionary shows that &_out = ANY('{src1,src2,src3,src4}'::varchar[]).  If I manually type this out, I receive records back from the query.  If I use any type of macro to generate the string, I receive an error or a table with 0 records.   ... View more

I am attaching the log to this message and the code executed below.  In the log you will see the values of all of the macros created from the prompts and the explicit macros created within the program.  One macro that I created (&varList.), takes the macros created by the "match_src" multi-select prompt and places them in a string.  The program below runs with 0 errors, but returns only column headers with no data.   %let begDate=%str(%')%sysfunc(inputn(&date_range_min,date9.), yymmdd10.)%str(%'); %let endDate=%str(%')%sysfunc(inputn(&date_range_max,date9.), yymmdd10.)%str(%');   data _null_;  length varList $1000;  do i=1 to &match_src_count.;   if i=1 then do;    varList=catx(',', varList, symget("match_src"));   end;   else do;    varList=catx(',', varList, symget(cats("match_src", i)));   end;  end;  call symputx('varList', varList); run;   %put &varList.;   proc sql; CONNECT TO GREENPLM AS gpcon (server="gp-db" db=db port=1 user=&Enter_Username password=&Enter_Password); CREATE TABLE test AS  (  SELECT * FROM connection to gpcon   (select *      FROM  data.prim_tbl   WHERE id in      (select id       from &sep_tbl.)   AND (record_ts::date <= &endDate.   AND source = ANY('{&varList.}'::varchar[])  /* The original SQL code had the following hard coded:  source = ANY('{src1,src2,src3,src4,src5}'::varchar[]) */ /* The macro "&varList." places the same exact text within the {} but returns column headers with no output */   )  ); quit; ... View more

When I change the single quotes to double quotes, I get the following:   ERROR:  CLI prepare error:  [SAS] [ODBC Greenplum Wire Protocal driver][Greenplum]ERROR:   column "{src1,src2,src3,src4}" does not exist. ... View more

Marcos removed per request.  If I take the same code below and paste it into the program we use for SQL on Greenplum, it runs with no problem.   proc sql; CONNECT TO GREENPLM AS gpcon (server="gp-db" db=db port=1 user=xxxx password=xxxx); CREATE TABLE test AS  (  SELECT * FROM connection to gpcon   (select *      FROM  data.prim_tbl   WHERE id in      (select id       from data.sec_tbl)   AND (record_ts::date <= '2018-08-10'   AND source = ANY('{src1,src2,src3,src4}'::varchar[]) /*  src1, src2, src3, etc are actually hard coded source names.  They are not the macro variables.  I would like to replace those with the actual macro variables.  */   )  ); quit; ... View more