Use a multi-selection macro variables from a prompt in pass through SQL

[anballa]

Good morning,

I am trying to automate a program to with prompts, and right now, the only part I am stuck with having it grab multiple marco variables created by a multiple selection prompt and use it in the SQL pass-through.  I have looked extensively online for code examples, but I have not found any that actually work.  I have tried creating macro's to take the macro variable and turn them into a string, but it still does not work.  As you see in the code below, I want to bring back records where the values selected by the prompt are in the column labeled "source".

Any help is appreciated.  Thanks!

%let begDate=%str(%')%sysfunc(inputn(&date_range_min,date9.), yymmdd10.)%str(%');
%let endDate=%str(%')%sysfunc(inputn(&date_range_max,date9.), yymmdd10.)%str(%');

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('{src1,src2,src3,src4}'::varchar[]) 

/* The number of "src" variables generated by the prompt depend on how many the person selects */
  )
 );
quit;

[ballardw]

Can you post an example of the code that worked without any of the macro variables involved? Replace your user and password with something like XXXX and YYYY.

It would also help to show the actual values of &date_range_min and &date_range_max

[anballa]

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;

[ballardw]

Now that we know what code should be generated can you explain how the code involving the macros "does not work".

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide examples.

[anballa]

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;

[Haikuo]

This will just be a wild guess: in your code: ANY('{src1,src2,src3,src4}'::varchar[])

If you plug in macro variable in place of src1-src4, it wouldn't work as is. the single quote will prevent macro variable from resolving. Try using double quotes instead if Greenplum SQL allows:

ANY("{&src1,&src2,&src3,&src4}"::varchar[])

[anballa]

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.

[Haikuo]

I have little to zero knowledge about PostgreSQL, so there maybe an issue around using double quotes instead of the single quotes. however, if that is the case, it boils down to delivering the right code content to 'pass-thru', for which SAS Macro is invented. 

So in the case of your code as following: 

AND source = ANY('{&varList.}'::varchar[]) 

There is another way to deliver it as wrapping everything on the right of the = sign (well, you don't have to wrap everything, this is just an example) into a compound macro variable (and you can also choose to concatenate the contents directly without making new macro variables, this is for easy reading)

/*construct your macro variable*/
%let _pre=%str(ANY%(%'{);
%let varlist=src1,src2,src3,src4;

%let _post=%str(}%'::varchar[]%)) ;

%let _out= %unquote(&_pre.&varlist.&_post);

Now your code will be like:

AND source = &_out.;

[anballa]

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.

[Haikuo · Accepted solution]

I am not entirely sure how you code flows, but if it runs at the order you presented, it won't work. When the &_out. is assigned, &varlist needs to be available. So it goes like this:

you initiate your prompts and create your varlist here or anywhere before the assigning &_out. Then:

%let _pre=%str(ANY%(%'{);

%let _post=%str(}%'::varchar[]%)) ;

%let _out= %unquote(&_pre.&varlist.&_post);

So the &_out assignment needs to be the last. But again, it might due to something else, this is just from SAS Macro perspective. Good luck.

Haikuo

[anballa]

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