BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anballa
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

 

View solution in original post

10 REPLIES 10
ballardw
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2441 views
  • 0 likes
  • 3 in conversation