Macro Variable within Proc SQL Open Query PostgreSQL

Reply
Contributor
Posts: 70

Macro Variable within Proc SQL Open Query PostgreSQL

Trying to get a macro variable to resolve within a Open query statement through SQL Server to Postgres.

Hello everyone,

I'm trying to use a open query statement to select a value from a Postgres database through SQL Server but the macro variable is not resolving.

1). Does not work as Postgres needs single quotes around a character field to resolve the query (SAS 9.4 DI 4.7)

This is the Open Query below;

PostgreSQL Pass Through Open-query:

(SELECT column_name FROM OPENQUERY(OFR_META_DB, 'select column_name from database_name.table_name where meta_name='''&meta_Name'' and state = ''Done'' order by row_id desc limit 1'));

2). Works (SAS 9.3 DI 4.6)

When selecting the data from MySQL through SQL Server I didn't have this issue as double quotes would resolve the macro variable and return the value from the query.

MySQL Pass Through OpenQuery:

SELECT column_name INTO :column_name FROM CONNECTION TO SQLDB

(SELECT column_name FROM OPENQUERY(MYSQLDEV, 'select column_name from database_name.table_name where meta_name="&meta_Name"  and state = "Done" order by rowid desc limit 1'));

Could this be something with an environment setting?

We moved from 9.3 >> 9.4

Any suggestions

Jonathan

Super User
Super User
Posts: 7,039

Re: Macro Variable within Proc SQL Open Query PostgreSQL

This a common issue because other languages are not as user friendly as SAS.

Just convert your macro variable value to include the single quotes.

Here is link to an example %SQUOTE() macro that you could use for that. 

Another way is to use take advantage that SAS determines whether to expand macro expressions based on the outer quotes.  Enclose the full string in double quotes and then use DEQUOTE() function to remove them.

%let meta_name=DATABASES;

%let myquery =

"'select column_name from database_name.table_name

where meta_name=''&meta_Name''

and state = ''Done''

order by row_id desc limit 1

'";

%put &myquery;

%put %sysfunc(dequote(&myquery));

118  %put &myquery;

"'select column_name from database_name.table_name where meta_name=''DATABASES'' and state = ''Done'' order by row_id desc limit 1'"

119  %put %sysfunc(dequote(&myquery));

'select column_name from database_name.table_name where meta_name=''DATABASES'' and state = ''Done'' order by row_id desc limit 1'

Contributor
Posts: 70

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Sounds good I will give that a try.

I did before hand add two single quotes around the macro variable like so but the marco variable still would not resolve.

%let datasetName = %str('')%sysfunc(strip(&meta_Name))%str('');

Contributor
Posts: 70

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Morning Tom,

Running the %put %sysfunc(dequote(&myquery)); function is not removing the double quotes from the query statement.

Within the log the double quotes are not being removed. I"m using the code editor within SAS Data Integration Studio

I'm running this statement between

proc sql;

quit;

Super User
Super User
Posts: 7,039

Re: Macro Variable within Proc SQL Open Query PostgreSQL

I have no idea about SAS Data Integration Studio.  Does it generate a SAS log?  Can you show the actual code that was run?  In your previous example you appeared to be passing code as a string to a function or procedure inside of the database.  Remember that if the outer quotes of the text that SAS sees are single quotes then neither macro variable references (&xxx) or macro functions or macro calls (%xxx) will be resolved.

So if you tried to do something like:

OPENQUERY(MYSQLDEV, '%sysfunc(dequote(&myquery))')


Then it will not work.  You will be passing the % and & and the rest of the string inside of the single quotes to the OPENQUERY() function.

That is why I wanted you to place the single quotes inside the double quotes so they are NOT included in the code.


%let myquery="'select .......'";

....

Then your call to OPENQUERY() would look like this:

OPENQUERY(MYSQLDEV, %sysfunc(dequote(&myquery)))


Or this:

OPENQUERY(MYSQLDEV, %sysfunc(dequote("'select ...  '")))

Contributor
Posts: 70

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Hey Guys here is the code.

I'm able to call the macro from within the proc sql statement.

The SQL statement and the macro variable resolves properly.

I am still getting an error but much closer.

****************************************************************************************************************************************************************

%macro sqlstatement;

  %let myquery = (SELECT column_name FROM OPENQUERY(META_DB, %sysfunc(dequote("'select column_name from ofr_registry.dataset_feed_state where dataset_name=&meta_name and state = ''Done'' order by row_id desc limit 1'));"));

  %put &myquery;

/* %put %sysfunc(dequote(&myquery));*/

%mend sqlstatement;

/*GET meta_name BY QUERYING DATASET DB*/

%let datasrc = dbname;

%let schemaName = schemaName;

%let Ref1 = Ref1;

LIBNAME &Ref1 SQLSVR  Datasrc=&dbname  SCHEMA=&schemaName  AUTHDOMAIN="SQLSRVAuth" ;

proc sql;

  CONNECT TO SQLSVR AS SQLDB

    (datasrc="&datasrc" AUTHDOMAIN="SQLSRVAuth");

  SELECT EXPRSSN INTO :meta_name FROM CONNECTION TO SQLDB

  (SELECT DB_NAME());

%put &meta_name;

%let meta_name = %str('')%sysfunc(strip(&meta_name))%str('');

%put &meta_name;

SELECT column_name INTO :column_name  FROM CONNECTION TO SQLDB

%sqlstatement;

%put &column_name;

  DISCONNECT FROM SQLDB;

quit;

****************************************************************************************************************************************************************

Log Error

****************************************************************************************************************************************************************

        %sqlstatement;

(SELECT column_name FROM OPENQUERY(META_DB, 'select column_name from database_name.table_name where

dataset_name=''Databases'' and state = ''Done'' order by row_id desc limit 1'));

                       _

                        79

ERROR 79-322: Expecting a (.

84        

                        _

                        76

ERROR 76-322: Syntax error, statement will be ignored.

Super User
Posts: 5,500

Re: Macro Variable within Proc SQL Open Query PostgreSQL

The use of %STR to quote an unmatched double quote is incorrect.  It should be %STR(%") instead.  As a result, the first ) is part in quotes and is part of the string being quoted, rather than ending the %STR function.  Even with the right syntax, however, quoted quotes can be a problem for SQL.

Not with total confidence, but I would try next replacing your assignment of &meta_name as follows:

%put &meta_name;

%let meta_name = &meta_name;

%let meta_name = "&meta_name";

%put &meta_name;

It looks like double quotes are sufficient for what you need at that point, and it looks like your results are resolving &meta_name within the WHERE clause.

The first %LET is sufficient to strip out leading and trailing blanks.

Trusted Advisor
Posts: 3,212

Re: Macro Variable within Proc SQL Open Query PostgreSQL

You are facing the famous macro quoting: SAS(R) 9.3 Macro Language: Reference 

There are a lot ways to solve that.

When using SAS- base a simple approach could be not using the macro processor for generating a line of code.

For example using a datastep to build up the character string and have that processed. dosubl and call execute SAS(R) 9.4 Functions and CALL Routines: Reference, Second Edition.

You are however mentioning DI 4.6 and DI 4.7 that is the interactive tool generating SAS-code (data integration studio) using "transformations".

In that case your infomration of the situation 9.3 vs 9.4 is not very clear inventarized.

What is the input (explicit/implicit), metadata defined library or not, explicit pst or implict.

What is the used transfomration etc....

---->-- ja karman --<-----
Contributor
Posts: 70

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Jaap,

Thank you for the information.

I have much to learn in this respect obviously.

Jonathan

Super User
Posts: 5,500

Re: Macro Variable within Proc SQL Open Query PostgreSQL

If all you need is to resolve a macro variable within single quotes, this would do it:

where meta_name = %unquote(%str(%'&meta_name%')) and state='Done' ...

Contributor
Posts: 70

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Posted in reply to Astounding

Could be doing something incorrectly but I can get the unquote to work outside of the proc sql but not within it ... .

Super User
Posts: 5,500

Re: Macro Variable within Proc SQL Open Query PostgreSQL

Then do it that way.  Outside of PROC SQL:

%let meta_name_quoted = %unquote(%str(%'&meta_name%'));

Inside PROC SQL:

where meta_name = &meta_name_quoted ...

Hard to tell why it doesn't work inside though.

Trusted Advisor
Posts: 3,212

Re: Macro Variable within Proc SQL Open Query PostgreSQL

As the 1/ situation is:

- moving 9.3 -> 9.4 and

- moving SQL-server -> postgres

- moving from expilicit SQL (with sql server)  to implicit SQL with postgres
There are a lot of transitions involved. unclear is where &Meta_name is coming from.

When this is hand-coded SQL or generated SQL makes a big difference how to solve it.  

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 12 replies
  • 1528 views
  • 1 like
  • 4 in conversation