topic Re: Using macro for proc sql with single and double quotes,error in SAS Procedures

Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 06:49:16 GMT

I am trying to do following in macro:

proc sql;

select * from table1 where col1 like 'x%'

quit;

%macro temp(val=x);

proc sql;

select * from table1 where col1 like '&val%';

quit;

%mend;

The problem is that to resolve the value of val, it has to be in double quotes(" ") , but sql statement works only with single quotes(' '), as it throws the error with double quotes: Invalid column name.

Any suggestion how to resolve this?

Re: Using macro for proc sql with single and double quotes,error

BrunoMueller — Thu, 11 Jun 2015 06:54:13 GMT

The double quotes should work just fine in Proc SQL, since SAS accepts either single or double quote. You are missing a semicolon at the end of the SELECT statement.

If you want to go with the single quotes, you can use the %TSLIT() autocall macro like below:

%macro temp(val=A); 

  proc sql;
    select * from sashelp.class where name like %tslit(&val%);
  quit;

%mend; 

%temp(val=A)

Bruno

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 08:30:09 GMT

Thanks, the semi-colon was missing by mistake here. Seems like %tslit is doing the job as expected. But not sure why double quotes throws the error: Invalid column name, even if i directly pull the data, using double quotes. I am using SQL server 2008, and query is in SAS.

Re: Using macro for proc sql with single and double quotes,error

BrunoMueller — Thu, 11 Jun 2015 08:33:40 GMT

If you post the SAS log it is easier to see what is going on

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 09:31:49 GMT

proc sql;

SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%";

quit;

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name

'Lialda%'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

The above statement works fine with single quotes.

Re: Using macro for proc sql with single and double quotes,error

RW9 — Thu, 11 Jun 2015 09:45:33 GMT

It looks like you are accessing and SQL Server database using pass-through. Why not use one SQL to extract all the data into SAS, then have your code filtering the data as it appears in SAS, otherwise you are calling the ODBC interface each time.

Re: Using macro for proc sql with single and double quotes,error

AhmedAl_Attar — Thu, 11 Jun 2015 10:04:03 GMT

Try this

%macro temp(val=x);

%local quotedVal;

%let quotedVal = %str(%')&val%nrstr(%%%');

proc sql;

select * from table1 where col1 like %unquote(&quotedVal); /* %superq(quotedVal) would cause SQL syntax Error! */

quit;

%mend;

options mprint;

%temp(val=x);

Re: Using macro for proc sql with single and double quotes,error

JakobHommelhoff — Thu, 11 Jun 2015 10:29:33 GMT

I have had the same problem...

Try this...

%macro temp(val=x);

proc sql;

select * from table1 where col1 like "%QOUTE(%%)&val.%";

quit;

%mend;

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 10:29:51 GMT

(You mean this statement creating problem:

proc sql;

connect to odbc as mycon

(datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%");

quit;

I am not much familiar with database interfacing, but this is what we usually do to connect to SQL server.

Re: Using macro for proc sql with single and double quotes,error

Tom — Thu, 11 Jun 2015 10:32:06 GMT

The query you posted appears to be reading from the SAS dataset WORK.OPS_SCOPE. So why are you getting an error message from the SQL Server Driver?

Is WORK.OPS_SCOPE a view? How is the view defined?

Most likely what you actually ran was a passthu query more like:

connect to odbc (....). as X ;

select * from connection to X (select scopeid from ops_scope where scope_desc like "XXX") ;

In that case the code that you send to the SQL Server needs to use SQL Server syntax and not SAS syntax.

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 10:41:47 GMT

OPS_SCOPE is a view. So, i think thats why its SQL server syntax.

Re: Using macro for proc sql with single and double quotes,error

Tom — Thu, 11 Jun 2015 10:55:43 GMT

So how is the view defined?

I just tried making a view to a table in Teradata server and calling it with a view and SAS happily translated my double quotes to single quotes for me.

libname td teradata ..... ;

proc sql ;

create view myview as select * from td.mytable ;

select * from myview where myvar like "mystr%" ;

Re: Using macro for proc sql with single and double quotes,error

RW9 — Thu, 11 Jun 2015 11:45:59 GMT

Well, the point I was trying to make was to extract the data into SAS without any restrictions:

proc sql;

connect to odbc as mycon

(datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope);

quit;

Now you can do one of two things, either in the SAS part of the query put the where - this would be if thats the only data you want to work on in SAS:

proc sql;

connect to odbc as mycon

(datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope)

where scope_desc like "Lialda%";

quit;

The where is then executed in SAS.

Alternatively, just keep the full dataset, and where off into a separate table in SAS, that way if you wanted other data, you wouldn't have to do the query again as all the data is in SAS. It really depends on if you need the other data.

Re: Using macro for proc sql with single and double quotes,error

Ksharp — Thu, 11 Jun 2015 11:59:04 GMT

That is weird . Your code should be work.

Try:

Code: Program


data table1;
col1='xdw';y1=2;output;
col1='ddw';y1=2;output;
run;


%macro temp(val=x);
proc sql;
select * from table1 where col1 eqt symget('val');
quit;
%mend;

%temp

Xia Keshan

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 12:42:37 GMT

I am not sure how view is defined, but

%tslit macro function stated above, seems to be doing the job for me.

Re: Using macro for proc sql with single and double quotes,error

munitech4u — Thu, 11 Jun 2015 12:44:39 GMT

I got your point. But if it happens to be large data, it would be waste of space and even take lot time, while i need only specific matching rows.

Re: Using macro for proc sql with single and double quotes,error

PaigeMiller — Thu, 11 Jun 2015 12:56:34 GMT

I just did a search for documentation on the %tslit macro and didn't find any. Someone point out where this is documented. Thanks!

Re: Using macro for proc sql with single and double quotes,error

Tom — Thu, 11 Jun 2015 13:07:13 GMT

You can also just make your own macro to add the single quotes.

Re: Using macro for proc sql with single and double quotes,error

AhmedAl_Attar — Thu, 11 Jun 2015 13:07:21 GMT

It's a SAS 9.4 autocall macro, 25076 - Resolve a macro variable within single quotation marks